CODEWITHSUNDEEP
mysqlambiguous
Asaf
Asaf

Reputation: 8206

Ambiguous column in MySQL even with Alias

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?

Upvotes: 3

Views: 987

Answers (3)

Devart
Devart

Reputation: 121912

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.

Upvotes: 0

Paddy
Paddy

Reputation: 33857

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';

Upvotes: 3

Book Of Zeus
Book Of Zeus

Reputation: 49877

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

Upvotes: 8

Related Questions