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
daniwao
13,125 PointsJoining Tables Challenge
The question is, "We have a 'movies' table with a 'title' and 'genre_id' column and a 'genres' table has an 'id' and 'name' column. Use an INNER JOIN to join the 'movies' and 'genres' tables together only selecting the movie 'title' first and the genre 'name' second."
My code that I've used is: SELECT * FROM movies INNER JOIN genres ON movies.title = genres.name;
I also tried: SELECT movies.title. genres.name FROM movies INNER JOIN genres ON movies.title = genres.name;
Can someone help!
4 Answers
thomascawthorn
22,986 PointsSo I think you're joining on the wrong columns.
The 'joining columns' are the genre_id in the genre table, and the genre_id in the movies table.
i.e. in movies table, genre_id = 2 - in genre table where genre_id = 2, the genre is action.
The join is created and from the combination of data that you've made, you're selecting the genre title.
I haven't checked this, but this is much closer to what you need... I think!
SELECT movies.title. genres.name FROM movies INNER JOIN genres ON movies.genre_id = genres.genre_id;
Yuan Tang
14,168 Pointstry LEFT INNER JOIN or RIGHT INNER JOIN in your second trial(also note that it's supposed to be a ", " instead of "." after SELECT movies.title). Let me know if this helps.
daniwao
13,125 PointsHi Yuan, I tried both your suggestions but it's giving me the same errors.
thomascawthorn
22,986 PointsSPOILER
If you give up, here's the right answer
SELECT movies.title, genres.name FROM movies INNER JOIN genres ON movies.genre_id = genres.id
daniwao
13,125 PointsAh man I used a "." instead of a "," and that's why it's been wrong since your hint. Thanks for the answer.
Ivan Bodnar
1,197 PointsThanks Tom Cawthorn, I also was stuck on this challenge.
daniwao
13,125 Pointsdaniwao
13,125 PointsHey Tom, I tried it but it didn't pass either. I'm not sure how you got to the part where the ON should be for movies.genre_id = genres.genre_Id;
Maybe I'm not reading the question right.
thomascawthorn
22,986 Pointsthomascawthorn
22,986 PointsIt's cool, no worries. Okay so in you're example you're saying:
SELECT movies.title. genres.name FROM movies INNER JOIN genres ON movies.title = genres.name;i.e. look at the movies table. Join it to the genres table where the column 'title' is = to the name column on the genres table.
This wouldn't return anything because you are saying where "James Bond" = "Action". This relationship doesn't exist which is why you're not getting any success.
When you say
SELECT movies.title. genres.name FROM movies INNER JOIN genres ON movies.genre_id = genres.genre_id;you're telling mysql okay, here's the ID of a genre. (1, 2, 3 etc). This number represents and actual genre, but it's on a different table. So you're saying, okay movies table, you have a genre id column which is just a number. I want to find out the genre title. Go find the genre title from the genre table which has the same id as movies.genre_id.
I don't know the actual column names so make sure you have all the right spelling!
daniwao
13,125 Pointsdaniwao
13,125 PointsThanks Tom for the explanation. Even though it didn't pass as the "right" answer I still feel better cause I'm getting the query that you suggested.