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

David Curran
David Curran
7,682 Points

Challenge 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.

Steven Parker
Steven Parker
231,072 Points

We'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.

David Curran
David Curran
7,682 Points

https://teamtreehouse.com/library/querying-relational-databases/joining-table-data-with-sql/join-queries

When 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
David Curran
7,682 Points

Firstly, 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
Steven Parker
231,072 Points

I 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
Steven Parker
231,072 Points

It 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
Bruce McMinn
10,030 Points

So, 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
Kevin Gates
15,053 Points

Updated 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
Steven Parker
231,072 Points

They 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
vindhya2017
2,414 Points

It 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
David Curran
7,682 Points

Thank 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
vindhya2017
2,414 Points

Hi 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".