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

David Curran
David Curran
7,679 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
221,451 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,679 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,679 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
221,451 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
221,451 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,029 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
14,935 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
221,451 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,679 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".