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
Alfredo Prince
6,175 PointsWhat's wrong with this query?
For some reason I can't link to the to the challenge
Along with the People table, we also have a Media table with media_id, title, img, format, year and category. To JOIN "many" media items with "many" people, we use a Media_People table which contains a media_id to link to the Media table and a people_id to link to the People table.
Modify your SELECT to pull Media title for all items that are linked to People with the last name "Tolkien"
Here's my query
SELECT title FROM Media JOIN People On Media_People.media_id = Media.people_id WHERE fullname LIKE '%Tolkien';
2 Answers
Simon Coates
28,694 Pointsmedia_people is a junction table, and you haven't included it in your join. YOu want to join media to media_people and join that to people. So you need two joins and two ons. If you include a URL to the challenge, I can test the query and give you an exact answer. update: as stated, if you give me a link I can check it out, but you're probably looking at something similar to
SELECT title FROM Media JOIN Media_People On Media.media_id = Media_People.media_id JOIN People ON Media_People.people_id = People.people_id WHERE fullname LIKE '%Tolkien';
Steven Parker
231,261 PointsI didn't see this question initially because I filtered on "unanswered" questions.
But I posted an answer to the previous question.
Alfredo Prince
6,175 PointsThank you too Steven! I appreciate the help!
Alfredo Prince
6,175 PointsAlfredo Prince
6,175 PointsI appreciate the time you took to help me. That worked but I am a little confused as to why I had to use two "Join" and two "ON" could you explain a little more please? Under what circumstance would I have used only one "JOIN"?
Humble thanks.
Simon Coates
28,694 PointsSimon Coates
28,694 PointsThe things is that People and Media have a many to many relationship. One-to-one relationship and one-to-many relationships can be modelled with just a foreign key. Many to many relationship require a table that sits between the two tables, hence media_people. You need two joins because you are joining people with media_people and joining this to Media. Like i said the term for this kind of table is a junction table.