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.

Development Tools Database Foundations Joining Relational Data Between Tables in SQL Joining Tables and Aliasing

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

Thanks

3 Answers

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

Thank you so much Robert! I was beginning to rip my hair out because of this!!

:)

Aaron Munoz
Aaron Munoz
11,177 Points

I don't get why that last bit is genres.id and not 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.

Hey 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
Brian Polonia
25,139 Points

Hey Tom,

Your explanation really helped me understand this. Thanks!

  • Brian

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

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