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.
I've been trying to complete the task for this, and I'm sure I'm putting in the right command but it's not working
The task 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." and I'm using this command:
"SELECT * FROM movies INNER JOIN genres ON movies.title = genres.name"
But it keeps coming back with this message: "Bummer! You're not retrieving the movie 'title' first and the genre 'name' second. Use an INNER JOIN."
Can anyone shine some light on this please
Joe, I believe the genre.genre_id is not in the genres table it should be genre.id
SELECT movies.title, genres.name FROM movies INNER JOIN genres ON movies.genre_id = genres.id
Hey man! Joins are really easy once once you get the hang of them. Let me try and help!
For this specific example, you could think of it as replacing a number with a string.
On the movies table, you have something like movies.id (integer), movies.title (string) and movies.genre_id (integer). Obviously movie genres aren't numbers in real life - you need to translate these numbers into meaningful values.
You need another table to translate the numbers from movies.genre_id into actual genres. Here comes the genre table. Each row in the genre table has a table id (genre.id) and an actual genre (action, scifi) etc. so genre.id 1 = action, genre.id 2 = scifi etc.
Switch back to the movies table. Let's say you have a movie - movie.id = 1, movie.title = Indiana Jones, movies.genre_id = 1.
We already know (looking at the genre table) that there is a row with genre.id = 1 and genre.title = "action". Indiana Jones is indeed an action movie, in fact one of the best I've ever seen. So the movies.genre_id = 1 (which points to genre.id = 1 | genre.title = "action" in the genre table).
Both tables share genre_id, so this has to be the JOINing value.
In your current statement
SELECT * FROM movies INNER JOIN genres ON movies.title = genres.name
you will be selecting movies where movie.title (Indiana Jones) = genres.name (action). This won't return anything. What you need to do instead is JOIN the tables on their common fields - genre_id. Our one row in the genre table (the whole row) is 'assigned' to a row in the movie table, meaning you're assigning genre.title via the genre.id.
SELECT * FROM movies INNER JOIN genres ON movies.genre_id = genre.genre_id
Hey presto, the number in genre_id can now be translated to a user friendly genre title.
You will have to play around with the column titles, but this should hopefully point you in the right direction.