Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases

Jan Lundeen
Jan Lundeen
5,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?

Thanks,

Jan

2 Answers

Steven Parker
Steven Parker
216,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.

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,630 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
216,083 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.

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