Ambiguous Mysql Column

Question

I have this query

SELECT t.employee_id, t.timeinhour,t.timeouthour, 
SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(t.timeouthour, t.timeinhour)))) 
AS Duration FROM timesheets t 
INNER JOIN employeetimesheets et 
ON t.employee_id=et.employee_id
WHERE employee_id='6748372'
AND timeinyear='2017' 
AND timeinmonth='March' 
AND isWeekNumber='1'

It gives me this error

1052 - Column 'employee_id' in where clause is ambiguous

I have looked here and here but I'm not using all (*) so I don't understand why?

You have `t.employee_id` and `et.employee_id`, for you they are the same and hold the same information but MySQL doesn't jump to conclusions, you have to specify which one. Just referencing `employee_id` makes it ambiguous. — Havenard, Mar 31 '17 at 22:13

score 1 · Answer 1 · answered Mar 31 '17 at 22:08

The product of the tables from which you're selecting includes two columns of the same name, as evident here:

ON t.employee_id=et.employee_id

Both timesheets and employeetimesheets have that column. Since the JOIN clause will ensure that those two columns in the product will always have the same value, it might not really matter which you specify. Either one will do the job:

WHERE t.employee_id='6748372'

Though you may want to run a DESCRIBE on both and see if there's any difference in the performance.

score 1 · Answer 2 · answered Mar 31 '17 at 22:09

You must specify which table you're referring to. Try this instead, and note the updated WHERE line:

SELECT t.employee_id, t.timeinhour,t.timeouthour, 
SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(t.timeouthour, t.timeinhour)))) 
AS Duration FROM timesheets t 
INNER JOIN employeetimesheets et 
ON t.employee_id=et.employee_id
WHERE t.employee_id='6748372'
AND timeinyear='2017' 
AND timeinmonth='March' 
AND isWeekNumber='1'

score 1 · Accepted Answer · answered Mar 31 '17 at 22:10

You need define table alias in where section, like this

SELECT t.employee_id, t.timeinhour,t.timeouthour,
SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(t.timeouthour, t.timeinhour))))
AS Duration FROM timesheets t
INNER JOIN employeetimesheets et
ON t.employee_id=et.employee_id
WHERE t.employee_id='6748372'
AND t.timeinyear='2017'
AND t.timeinmonth='March'
AND t.isWeekNumber='1'

If column from table employeetimesheets set et alias

Ambiguous Mysql Column

1052 - Column 'employee_id' in where clause is ambiguous

3 Answers3