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

Databases

Keith Colleluori
Keith Colleluori
9,577 Points
Posted by Keith Colleluori
Keith Colleluori
9,577 Points

SQL SELECT title ON JOIN WHERE name

I am trying to get the SQL on this challenge and pretty much everything I try retrieves all the titles regardless of the authors name.

SELECT title FROM Media LEFT OUTER JOIN People ON Media.media_id AND People.people_id WHERE fullname LIKE '%Tolkien';

If I add SELECT fullname, to the query I am getting Tolkiens name next to every media listed.

The error message (from treehouse) is basically always asking me if I selected ONLY title from media and although the joins appear to be working and i am retrieving titles, it seems I am retrieving all titles and somehow assigning Tolkien as the people ID.

In case my question isn't explicit enough this is the Challenge's request.

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"

2 Answers

Keith Colleluori
Keith Colleluori
9,577 Points
Keith Colleluori
Keith Colleluori
9,577 Points

I figured out my problem was not doing my joins correctly. I looked up some examples on the web (http://www.w3schools.com/sql/sql_join.asp) and I decided that my syntax was wrong. I don't understand the weird results I was getting but I think I understand joins in general a little bit better right now.

robbertdekock
robbertdekock
13,390 Points
robbertdekock
robbertdekock
13,390 Points

Hi Keith, Your JOIN isn't completely correct as you say. I think you're missing one link. You are now connecting people id's to media id's which aren't the same. That's where te Media_People table comes in. You'll first need to connect the people_id from the People table to the people_id in the Media_People table. There you'll find the connecting media_id. Once you've connected those 2 tables you still need to connect the Media_People table to the Media table. Then you will retrieve the right results. Can you try the following code?

SELECT Media.title FROM People
JOIN Media_People ON People.people_id = Media_People.people_id
JOIN Media ON Media_People.media_id = Media.media_id
WHERE fullname LIKE '%Tolkien';