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
Billy Bellchambers
21,689 PointsPHP/SQL Joins confusion.
Hi,
"Challenge Task 2 of 3
Note: We will be writing ONLY the SQL query for this 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"
You can start with the following code from the last task:
SELECT * FROM People
WHERE fullname LIKE '%Tolkien';"
I am really struggling with this question and unsure exactly what the question wants?
Might just be me but don't feel its worded very well.
This is what I have managed to get but think I am way off the mark, any assistance would be great!
SELECT title FROM People
WHERE fullname LIKE '%Tolkien'
JOIN Media_People ON Media_People.media_id=Media.media_id,
JOIN Media ON Media.people_id=People.people_id
;
7 Answers
Billy Bellchambers
21,689 PointsThanks Alex that helped a bunch still wasn't 100% what the code challenge was after but what you provided got me in the ball park to get it right.
Correct answer was.
SELECT title FROM People
JOIN Media_People ON Media_People.media_id=Media.media_id
JOIN Media ON Media_People.people_id=People.people_id //Media needed to link onto Media_People not Media
WHERE fullname LIKE '%Tolkien';
Regardless that helped a bunch so thank you!
Petrov von Petrov
21,916 PointsYou have to pull 'title' from the Media table, not the People table. So it should be like this:
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';
Amber Stevens
Treehouse Project ReviewerJust wanted to say that I've tried, both this solution and the one Billy Bellchambers said was the solution up above and they BOTH work....
Kazi Ahmed
11,367 PointsSELECT title FROM People JOIN Media_People ON Media_People.media_id=Media.media_id JOIN Media ON Media_People.people_id=People.people_id WHERE fullname LIKE '%Tolkien';
Alex Hedley
16,381 PointsYou are close, you just need to re-arrange the ordering of your statements The WHERE needs to be last You also don't separate the lines with a comma (,)
SELECT title FROM People
JOIN Media_People ON Media_People.media_id=Media.media_id
JOIN Media ON Media.people_id=People.people_id
WHERE fullname LIKE '%Tolkien';
Robert Mukamba
10,774 PointsHi there, This Worked For Me SELECT title FROM People JOIN Media_People ON Media_People.media_id=Media.media_id JOIN Media ON Media_People.people_id=People.people_id //Media needed to link onto Media_People not Media WHERE fullname LIKE '%Tolkien'; Please Note Remove the comment, the it will pass
Robert Mukamba
10,774 PointsSELECT title FROM People JOIN Media_People ON Media_People.media_id=Media.media_id JOIN Media ON Media_People.people_id=People.people_id WHERE fullname LIKE '%Tolkien';
Junhan Liu
5,582 PointsSELECT title, category FROM Media JOIN Media_People ON Media_People.media_id = Media.media_id JOIN People ON Media_People.people_id = People.people_id WHERE fullname LIKE '%Tolkien';
// match media with media_people first // then match media_people with people