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

Brett Phillips
Brett Phillips
7,016 Points
Posted by Brett Phillips
Brett Phillips
7,016 Points

Many-to-Many join? What are we actually supposed to be doing here?

I'm totally stumped by this question. We're told to "Join the Media table to the Genres table using the Media_Genres table".

The schema shows this Media_Genres table serves as a many-to-many link between Media and Genres.

I'm on the Intermediate PHP track, which only required the Beginner SQL units. I don't feel this kind of JOIN was covered in those basic units. I'm out of my depth!

SELECT Media.title, Genres.genre FROM Media ??? (what goes here? Do I need to join Media to Genres, then Genres to Media_Genres?) WHERE Media.media_id=3;

The original question being: 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;

6 Answers

Julie Dowler
Julie Dowler
7,851 Points
Julie Dowler
Julie Dowler
7,851 Points

The second INNER JOIN needs to join the Genres table. You've already got the Media_Genres table joined, and now you need to join the Genres table: SELECT title FROM Media INNER JOIN Media_Genres ON Media_Genres.media_id = Media.media_id INNER JOIN Genres ON Media_Genres.genre_id = Genres.genre_id WHERE Media.media_id = 3; (I see I made a mistake in my statement in my first comment, too. I forgot to name the table in my second INNER JOIN statement).

Brett Phillips
Brett Phillips
7,016 Points
Brett Phillips
Brett Phillips
7,016 Points

Thanks so much Julie! Still getting my head around that, but I'm sure with further practice and more examples it'll click.

Julie Dowler
Julie Dowler
7,851 Points
Julie Dowler
Julie Dowler
7,851 Points

Media_Genres is the table that has data which matches Media, and data which matches Genres. Join Media_Genres to Media, then join Media_genres to Genres.

SELECT Media.title, Genres.genre FROM Media INNER JOIN Media_Genres ON Media_Genres.media_id = Media.media_id INNER JOIN Media_Genres.genre_id=Genres.Genre_id WHERE media_id=3

As far as the SELECT goes, I'm copying what you had, even though from what I see of the question here, they're not asking for you to select specific information. You might want to just use *.

Brett Phillips
Brett Phillips
7,016 Points
Brett Phillips
Brett Phillips
7,016 Points

Thanks for the advice. I was using the specific "Media.title, Genres.genre" in my SELECT statement in an attempt to narrow it down and try to get any kind of result.

I have tried your suggestion (and some variations), but still no luck.

For instance, the following SELECT statement:

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

...gives the following error:

SQL Error: ambiguous column name: Media_Genres.media_id

I'm at a loss how explicitly named columns are flagged as "ambiguous".

Julie Dowler
Julie Dowler
7,851 Points
Julie Dowler
Julie Dowler
7,851 Points

I haven't found any place on Treehouse yet where they explain JOINs very well. It made sense to me after I read this: (https://www.w3schools.com/sql/sql_join.asp)

Anthony Meyer
Anthony Meyer
2,472 Points
Anthony Meyer
Anthony Meyer
2,472 Points

I ended up figuring it out. The change I made was what I selected. The question asks for all columns, so specifically running "SELECT title... " doesn't work, which is what I had tried for some dumb reason.

Charles Fields
Charles Fields
4,205 Points
Charles Fields
Charles Fields
4,205 Points

I had much the same experience, and eventually got a similar query accepted with INNER JOIN statements. To do so I had to remove my aliases and use full table names. In the process I removed parentheses around the ON table.column = .. clauses. This should not matter, but then again neither should the aliases. I find the pickiness of these challenges and lack of detail in the error messages a very annoying combination. Syntax error! What??

Anthony Meyer
Anthony Meyer
2,472 Points
Anthony Meyer
Anthony Meyer
2,472 Points

Is there a specific reason this requires an Inner Join as opposed to a "Join"? My code was identical to Julie's with the exception of not having the "inner" in the join and was coming up as "wrong", but was giving the correct information.

Brett Phillips
Brett Phillips
7,016 Points
Brett Phillips
Brett Phillips
7,016 Points

Good question Anthony.

I don't think it should matter, as the keyword "INNER" appears to be optional (but perhaps good practice, being more explicit?)

I would recheck your code, because I just went back and tried my SQL statement without "INNER" and it still worked.

My code:

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

It might depend on the database server you're using too. Some databases might require "INNER", while others exercise greater leniency (like SQLite apparently does).