Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Jan Lundeen
Jan Lundeen
5,881 Points
Posted by Jan Lundeen
Jan Lundeen
5,881 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?

Thanks,

Jan

2 Answers

Steven Parker
Steven Parker
229,732 Points
Steven Parker
Steven Parker
229,732 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.

The INNER JOIN of the CAR table isn't needed here. Once you remove that, VIN will no longer be ambiguous.

And MODEL should be the first (left) table so rows will be shown even if they don't match with the CAR table.

Jan Lundeen
Jan Lundeen
5,881 Points
Jan Lundeen
Jan Lundeen
5,881 Points

Hi Steven,

By assigning an alias of c to the CAR table and m to Model table, I thought I qualified the columns properly(e.g. c.ModelID, m.ModelID). I didn't realize that joining the CAR table twice would do that. Thanks for setting me straight.

Okay. Since it's a LEFT OUTER JOIN, it will show all the Model table records (since it's on the left) and only the matches from the table on the right (Car table).

Thanks for your help!

Jan

Steven Parker
Steven Parker
229,732 Points
Steven Parker
Steven Parker
229,732 Points

You're right that you can use a table alias to disambiguate column names, but you would have needed "c.VIN" instead of just "VIN".

But you should always assign unique alias names to each join. Since both joins used "c" as the alias, it would not have been sufficient to fully qualify the column. Even though it's the same table, the join criteria would have caused the column content to be potentially different.

Dulsie fadzai Mudekwa
Dulsie fadzai Mudekwa
8,656 Points
Dulsie fadzai Mudekwa
Dulsie fadzai Mudekwa
8,656 Points

SELECT ModelName, VIN FROM Model AS m LEFT OUTER JOIN Car AS c ON m.ModelID = c.ModelID;