Ambiguous column in MySQL even with Alias

Question

I have the following MySQL:

select  `car`.`ID` AS `ID`,
    `title`,`text`
    from `car` 
    LEFT JOIN `truck` as bigcar ON bigcar.`ID` = `car`.`truckID` 
    WHERE `ID` ='1';

For some reason I'm getting

Column 'ID' in where clause is ambiguous

For complicated code reasons, I'm not renaming the alias as something else (such as ID2)
Shouldn't this work?

score 8 · Accepted Answer · answered Nov 25 '11 at 14:31

You need to specify the table you are using for id in your the where clause. Otherwise the query will know not which one to use since you have the same column name in two tables

SELECT
 `car`.`ID` AS `ID`, `title`,`text`
FROM `car` 
LEFT JOIN `truck` as bigcar ON bigcar.`ID` = `car`.`truckID` 
WHERE `car`.`ID` ='1';

or if you want the bigcar ID you will have to use:

WHERE `bigcar`.`ID` ='1'; // not truck but bigcar which is the alias you created

score 3 · Answer 2 · answered Nov 25 '11 at 14:31

3

What about:

select  `car`.`ID` AS `ID`, 
    `title`,`text` 
    from `car`  
    LEFT JOIN `truck` as bigcar ON bigcar.`ID` = `car`.`truckID`  
    WHERE `car`.`ID` ='1';

answered Nov 25 '11 at 14:31

Paddy

33,309
15
79
114

@Martin Smith - I'm not a MySql head, but I don't get why? – Paddy Nov 25 '11 at 14:58
er. whoops it won't convert it into an inner join at all. Comment deleted! It would have done if the where clause was on the right hand table. – Martin Smith Nov 25 '11 at 15:00

score 0 · Answer 3 · answered Nov 25 '11 at 14:34

0

The error means the field ID exists in both tables. To fix it you should add a table alias to WHERE clause, e.g. - bigcar.ID = 1 or car.ID = 1 ...as you need.

answered Nov 25 '11 at 14:34

Devart

119,203
23
166
186

Ambiguous column in MySQL even with Alias

3 Answers3