CODEWITHSUNDEEP
mysqlambiguous
Sebastian Farham
Sebastian Farham

Reputation: 825

Ambiguous Mysql Column

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?

Upvotes: 0

Views: 943

Answers (3)

Gedweb
Gedweb

Reputation: 739

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

Upvotes: 1

Aidan Fitzpatrick
Aidan Fitzpatrick

Reputation: 2035

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'

Upvotes: 1

David
David

Reputation: 218828

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.

Upvotes: 1

Related Questions