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
Joe Hilton
Courses Plus Student 4,893 PointsI'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
Thanks
3 Answers
Robert Mabbs
12,060 PointsJoe, 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
thomascawthorn
22,986 PointsHey 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.
Joe Hilton
Courses Plus Student 4,893 PointsHey Tom, thanks for the reply. I've tried altering the column titles but it's still not working. Now it's coming up with this error message: "4c1fa4f3-cbec-4680-9f92-5a8bd3182c08.rb:38:in eval': undefined methodto_a' for # (NoMethodError) from 4c1fa4f3-cbec-4680-9f92-5a8bd3182c08.rb:38:in eval' from 4c1fa4f3-cbec-4680-9f92-5a8bd3182c08.rb:38:in"
Brian Polonia
25,139 PointsHey Tom,
Your explanation really helped me understand this. Thanks!
- Brian
Robert Mabbs
12,060 PointsJoe Hilton take another look at your query the question is asking you to inner join by using the movies.genre_id to the genres.id and to select only movies.title then genres.name.
Hope this helps.
Jim Withington
12,025 PointsI think that the confusing part was the wording of
selecting the movie 'title' first and the genre 'name' second."
This wasn't really worded this way earlier (I don't think), so it seemed overly complex/confusing for the code challenge.
Joe Hilton
Courses Plus Student 4,893 PointsJoe Hilton
Courses Plus Student 4,893 PointsThank you so much Robert! I was beginning to rip my hair out because of this!!
:)
Aaron Munoz
11,177 PointsAaron Munoz
11,177 PointsI don't get why that last bit is genres.id and not genres_id .