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

PHP

Posted by Titus Salmon
Titus Salmon
19,076 Points

Joining 2 tables using a 3rd: Code Challenge 1 in Integrating PHP with Databases course

Am unable to figure out the correct answer to the following code challenge in the Integrating PHP With Databases course: Challenge Task 1 of 1 We will be writing ONLY the SQL query for this challenge. The library database contains a Media table with the columns media_id, title, img, format, year and category. It also contains a Genres table with the columns genre_id and genre. To join these tables, there is a Media_Genres table that contains the column media_id and genre_id Add to the following SELECT statement to JOIN the Media table and the Genres table using the joining table Media_Genres. SELECT * FROM Media WHERE media_id=3; NOTE: You will need to add the table to the WHERE clause so that the media_id column is not ambiguous.

My code: SELECT * FROM Media_Genres LEFT OUTER JOIN Genres ON Media_Genres.genre_id=Genres.genre_id LEFT OUTER JOIN Media ON Media_Genres.media_id=Media.media_id WHERE Media_Genres.media_id=3;

The error message thrown: Bummer! You need to JOIN the Media_Genres table. I thought I had already done this - that is, JOINED the Media_Genres table to both the Genres table and Media table?! Am I on the right track with this, and can you give me any direction on how to make this work? Thanks.

5 Answers

Joel Bardsley
Joel Bardsley
31,249 Points
Joel Bardsley
Joel Bardsley
31,249 Points

Hi Titus,

It looks like you've done things the wrong way around. The question is as follows:

Add to the following SELECT statement to JOIN the Media table and the Genres table using the joining table Media_Genres.

SELECT * FROM Media WHERE media_id=3;

You still want to SELECT * FROM Media whereas you're currently selecting everything from Media_Genres and are joining tables based off that. So you'll want to firstly join a table that shares a common field with the Media table, and then join that table with another table with a different common field.

In terms of actual joins it looks like you've got the right idea, however unless a question specifically says to use a LEFT OUTER JOIN, just stick with simple JOINs.

Good luck!

Titus Salmon
Titus Salmon
19,076 Points
Titus Salmon
Titus Salmon
19,076 Points

Thank you. Here's my latest attempt:

SELECT * FROM Media JOIN Genres ON Media_Genres.genre_id=Genres.genre_id JOIN Media ON Media_Genres.media_id=Media.media_id WHERE Media.media_id=3;

Which throws the following error: SQL Error: ambiguous column name: main.Media.media_id

This was actually one of my 1st attempts, and I've tried countless other combinations, but just can't get the code to pass.

Joel Bardsley
Joel Bardsley
31,249 Points
Joel Bardsley
Joel Bardsley
31,249 Points

Ok let's break it down and hopefully this will help clear things up:

SELECT * FROM Media
WHERE media_id = 3;

Above is the initial code from the question. First you'll need to join Media to a table that shares a common field. Ideally we'd join the Genres table but there's no media_id in that table to join with. However the Media_Genres table does have a media_id column, so let's use that:

SELECT * FROM Media
JOIN Media_Genres ON Media.media_id = Media_Genres.media_id
WHERE Media.media_id = 3;

As you'll know I've changed the WHERE clause to Media.media_id = 3 to avoid any ambiguous column name errors.

Next we need to join the Media_Genres table with the Genres table. Both tables have a common genre_id column so we are able to apply the join:

SELECT * FROM Media
JOIN Media_Genres ON Media.media_id = Media_Genres.media_id
JOIN Genres ON Media_Genres.genre_id = Genres.genre_id
WHERE Media.media_id = 3;

And that should pass the challenge. If you need anything clearing up, let me know.

Titus Salmon
Titus Salmon
19,076 Points
Titus Salmon
Titus Salmon
19,076 Points

Thanks, that worked. I don't fully understand why the following doesn't work, though:

SELECT * FROM Media JOIN Media_Genres ON Media.media_id = Media_Genres.media_id JOIN Media_Genres ON Genres.genre_id = Media_Genres.genre_id WHERE Media.media_id = 3;

Joel Bardsley
Joel Bardsley
31,249 Points
Joel Bardsley
Joel Bardsley
31,249 Points

For your second join, you're trying to join the Media_Genres table to the Media_Genres table, so the script doesn't know what Genres.genre_id is!

Titus Salmon
Titus Salmon
19,076 Points
Titus Salmon
Titus Salmon
19,076 Points

Hmm... But wouldn't that imply that in my 1st join I was also trying to join Media_Genres to Media_Genres? I thought my 2nd join was trying to join Media_Genres to Genres through the genres_id column common to both tables. Clearly, still learning...

Joel Bardsley
Joel Bardsley
31,249 Points
Joel Bardsley
Joel Bardsley
31,249 Points

If you strip away the ON clauses, you can see why the Genres table is never joined

SELECT * FROM Media
JOIN Media_Genres
JOIN Media_Genres /* joining the Media_Genres table again, so there's no reference to Genres for Genres.genre_id */
WHERE ...
Titus Salmon
Titus Salmon
19,076 Points
Titus Salmon
Titus Salmon
19,076 Points

Thanks for the help!