INNER JOIN and ORDER BY with same key gives ambiguous column name-CodePudding

I am getting the ambiguous colummn name error when trying to ORDER BY the key no_auto and inner joining the tables auto and location with the same key. This is an assignment where I cannot change the database I only do queries.The information selected comes from the tables modele and auto the where clause is from the table location and the ORDER BY is from the table auto and also the location one. I suppose that the error comes from using the order by and inner join with the same key , I tought about using alias but I don't know how. Thanks for the help.

INNER JOIN auto
ON modèle.code_modèle = auto.code_modèle
INNER JOIN location
ON auto.no_auto = location.no_auto
WHERE coût_location > 500
ORDER BY no_auto; ```

CodePudding user response：

The problem here is that you're ordering by no_auto, which exists in both the auto and location tables. Add prefix to no_auto on the last row (and select at the top preferably) and this will run without that error.

So, either:

SELECT *
INNER JOIN auto
    ON modèle.code_modèle = auto.code_modèle
INNER JOIN location
    ON auto.no_auto = location.no_auto
WHERE coût_location > 500
ORDER BY location.no_auto

Or:

SELECT *
INNER JOIN auto
    ON modèle.code_modèle = auto.code_modèle
INNER JOIN location
    ON auto.no_auto = location.no_auto
WHERE coût_location > 500
ORDER BY auto.no_auto

Not just 'no_auto', as it does not know which of the two 'no_auto' to use.