Question 20210 in Mysql Question posted in
The official MySQL documentation can be found here.

Getting ambiguity error in location column – Mysql

I am trying to run the below query , I am getting ambiguity error in location column . Can anyone please explain what is the logical error here

select firstname,lastname,location,total_count,avg_sal 
from employee join 
(
    select location,count(location),avg(salary) 
    from employee 
    group by location
) temp_table on employee.location=temp_table.location;
Tags:

3

Answers


  1. 0

    try putting employee.location in the select. Both your tables have a column called location and so the select statement doesn’t know which tables column called location you want.

    SELECT firstname, lastname, employee.location, total_count,avg_sal 
from employee 
join ( 
    select location, count(location), avg(salary) 
    from employee 
    group by location
) temp_table on employee.location = temp_table.location;
    Login or Signup to reply.
  2. 0

    Probably your issue is that the optimizer does not know from which table (employee or the derived temp_table) each colums refers, plus you are also missing the aliases in the derived table referred in the outer query – using aliases elimiates ambiguity, try:

    select e.firstname, e.lastname, e.location, t.total_count, t.avg_sal 
from employee e
join (
  select location, count(location) total_count, avg(salary) avg_sal
    from employee 
    group by location
) t
on e.location = t.location;
    Login or Signup to reply.
  3. 0

    Both the main employee table and the inner temp_table have a location field. You need to clarify which one you want in the select list.

    I know it seems like it shouldn’t matter, since they the fields are guaranteed to have the same value.

    on employee.location = temp_table.location;

    They even source from the same base table. But that’s not the whole story.

    To understand why, say you have a pair of rows like this:

    firstname lastname location salary
    Jane Doe new york 54
    John Smith New York 50

    The location in both of those fields is the same for purposes of the GROUP BY and ON conditional expressions, but for one of those rows the grouped (temp_table) location value will be different for purposes of the SELECT list.

    Which one should you show? The database can’t just assume either way which one you mean. You have to be explicit by prefacing the column with a table name or alias.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search