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 trialDavid Curran
7,682 PointsChallenge Task 2 of 5 on Databases
In a car database there is a Make table with columns, MakeID and MakeName, a Model table with columns, ModelID, MakeID and ModelName and a Car table with columns, CarID, ModelID, VIN, ModelYear and StickerPrice. For all cars in the database, show Make Name, Model Name, VIN and Sticker Price from the Model and Car tables in one result set.
David Curran
7,682 PointsWhen I run the SQL query I get the following SQL Error
SELECT Make.MakeName, Model.ModelName, Car.VIN, Car.StickerPrice FROM Make INNER JOIN Model On Make.MakeID = Model.MakeID INNER JOIN Model Car ON Model.ModelID = Car.ModelID
SQL Error: no such column: Car.VIN
David Curran
7,682 PointsFirstly, thank you for your help Steven. I am still a little confused. The challenge asks for Make Name along with Model Name, VIN and Sticker Price. The MakeName field only appears in the Make table. Therefore, do I not have to reference the name of the table after the SELECT keyword? This is why I have Make.MakeName in the SELECT list. The challenge has 3 tables, Make, Model and Car. I assumed from the question that the challenge wants some data from all 3 tables to be returned in the results set, hence return the Make Name from the Make table, the Model Name from the Model table and VIN and Sticker Price from the Car table. Am I misunderstanding the question?
Steven Parker
231,072 PointsI have updated my answer to reflect task 2 (it was previously based on task 1). And yes, both task 1 and task 2 require all 3 tables to be joined, as you have done.
5 Answers
Steven Parker
231,072 PointsIt looks like you just have a stray word "Model":
- your second JOIN says "
INNER JOIN Model Car
" but should be "INNER JOIN Car
" (no "Model")
Just remove that word and you should pass task 2.
Bruce McMinn
10,030 PointsSo, is the trick that you link PK in table one to a column in table two, PK in table two to a column in table three, etc?
SELECT Make.MakeName, Model.ModelName, Car.VIN, Car.StickerPrice FROM Make
INNER JOIN Model ON Make.MakeID = Model.MakeID
INNER JOIN Car ON Model.ModelID = Car.ModelID;
Kevin Gates
15,053 PointsUpdated if you want guidance to shorten table names:
SELECT mk.MakeName, md.ModelName, c.VIN, C.StickerPrice
FROM Make AS mk
INNER JOIN Model AS md
ON mk.MakeID = md.MakeID
INNER JOIN Car AS c
ON md.ModelID = c.ModelID;
Steven Parker
231,072 PointsThey are certainly a great feature, but a bit of explanation about table aliases along with the example might be helpful for folks who haven't seen them before.
vindhya2017
2,414 PointsIt should be a quick one to fix for you. You need to have both the tables in the FROM section. Add the CAR table and it should work. Let me know how you go! Good luck!
David Curran
7,682 PointsThank you vindhya2017 for your help. I am still not getting there. I thought I had to put the names of the fields I am wanting to return in my results set after the SELECT keyword. This is why I have written the following: SELECT Make.MakeName, Model.ModelName, Car.VIN, Car.StickerPrice You mention that I should include the Car table after the FROM keyword, should I also include the Make and Model tables as well? I have tried to write the query a few different ways now and I still can't get it to work.
SELECT Make.MakeName, Model.ModelName, Car.VIN, Car.StickerPrice FROM Make, Car INNER JOIN Model On Make.MakeID = Model.MakeID INNER JOIN Car ON Model.ModelID = Car.ModelID
vindhya2017
2,414 PointsHi David, in your original sql can you delete the model table name which is after the second inner join. It should be "inner join car" and not "inner join model car".
Steven Parker
231,072 PointsSteven Parker
231,072 PointsWe'd be happy to take a look, can you post your code here for us to see? And also please provide a link to the course page you were working with.