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!
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
Adrianne Padua7,506 Points
Challenge Step 1 - Joining/Aliasing Tables
I don't understand this question. Is it a different case with titles and names in comparison with using ids on two tables when you try to join them together using INNER JOIN? I typed the same command as the one with the ids and applied to the movies.title and genres.name and they always come out as an error. What am I missing here? Please help?
I used - SELECT * FROM movies INNER JOIN genres ON movies.title = genres.name;
I also used - SELECT * FROM movies LEFT INNER JOIN genres ON movies.title = genres.name;
iainwbCourses Plus Student 2,437 Points
Okay - I see why your join is failing.
When you use joins you need to use keys to make the join - that's the ID fields in this case. The keys are the things that link data from the two tables together.
You can choose to select the fields to display the more "friendly" data, but the matching is done on the ID fields.
SO: In themselves, the title value of a row in the movies table (movies.title) and a name value of a row in the genres table (genres.name) mean nothing to each other. It's the movies.genre_id value that matches the genres.id value - and by linking these together you can link the other data.
It's probably easier if I give you an example:
Let's take a line of data from the movies table first:
id | title | year_released | genre_id 1 | Alien | 1979 | 1
Now if we take three rows from the genres table:
id | name 1 | Sci Fi 2 | Action 3 | Musical
So if you look at the top table (movies) and the genre_id field (movies.genre_id), and use the value held their (1) and look that up in the bottom table (genres) and the id field (genres.id) you will see that the name is "Sci Fi". The reason for this is that movies.genre_id is a Foreign Key that matches genres.id which is the Primary Key for the genres table.
So - the way we do an INNER JOIN is to use the key fields that match each other:
SELECT * FROM movies INNER JOIN genres ON movies.genre_id = genres.id;
The above query will join the two tables together and the result show you the match between the movies.genre_id and genres.id fields - the first four columns are from the movies table, the last two are from the genres table:
id | title | year_released | genre_id | id | name 1 | Alien | 1979 | 1 | 1 | Sci Fi 2 | Aliens | 1986 | 1 | 1 | Sci Fi 3 | Moulin Rouge | 2001 | 3 | 3 | Musical 4 | Guys and Dolls | 1955 | 3 | 3 | Musical 5 | Mama Mia | 2008 | 3 | 3 | Musical 6 | Starman | 1984 | 1 | 1 | Sci Fi 7 | Tron | 1982 | 1 | 1 | Sci Fi 8 | Die Hard | 1988 | 2 | 2 | Action
What you can do then is choose not to pull back all the info - if all you want is the movie title and the genre name, you would use the following query:
SELECT movies.title, genres.name FROM movies INNER JOIN genres ON movies.genre_id = genres.id;
Hope that helps? :)
Adrianne Padua7,506 Points
Thanks for the better explanation, even though I actually figured this out a day or so later. The question was a little confusing at first (and it was late night when I was trying to figure out the answer), but I understood it more the next day. Thank you. :)