Welcome to the Treehouse Community
Looking to learn something new?
Jan Lundeen5,630 Points
In the Querying Relational Databases course (Challenge 4 of 5), getting SQL Error: ambiguous column name: VIN
In the Querying Relational Databases course (Challenge 4 of 5), I'm getting an error. Here's the question:
In a car database there is a Model table with columns, ModelID, MakeID and ModelName and a Car table with columns, CarID, ModelID, VIN, ModelYear and StickerPrice. Show all Model names from the Model table along with VIN from the Car table. Make sure models that aren’t in the Car table still show in the results!
Here's my query:
SELECT ModelName, VIN FROM Model AS m INNER JOIN Car AS c ON m.ModelID = c.ModelID LEFT OUTER JOIN Car AS c ON m.ModelID = c.ModelID;
In particular, I'm getting the following error:
"SQL Error: ambiguous column name: VIN"
I switched around the query so that Car was the first table. Then, I used Model as the first table. Both times, I got the same error. Since the last sentence says to Make sure models that aren’t in the Car table still show in the results!", I'm thinking that means that Car should be the first table when I do the LEFT OUTER JOIN. I also checked to make sure I didn't misspell the column names. I'm not sure why I'm getting this error. Any ideas?
Steven Parker216,083 Points
Columns must be fully qualified when not unique among joined tables.
Anytime you join two or more tables that have a column with the same name, you need to qualify the column name with a table name or alias. In this case,
VIN has become ambiguous because you have joined the
CAR table twice.
INNER JOIN of the
CAR table isn't needed here. Once you remove that,
VIN will no longer be ambiguous.
MODEL should be the first (left) table so rows will be shown even if they don't match with the
SELECT ModelName, VIN FROM Model AS m LEFT OUTER JOIN Car AS c ON m.ModelID = c.ModelID;