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

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

Trouble with Joins... Ambiguous WHERE statement?

I have been having a tough time piecing together this join. I have tried several (a dozen or more) combinations of the code below and can't really seem to figure out whats wrong. Most often I receive a defunct looking table, and ambiguous column error, or just am way off to recieve no meaningful details!! Not sure why we jumped straight into 2 part joins but I am running out of ideas to make this work.

I am trying to join media to media_genres and genres to media_genres and select * from media_genres WHERE media_genres.media_id = 3

But whats going on exactly? I don't know!!

2 Answers

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

I finally figured it out. Sometimes it takes writing it again from scratch to get it right!!

My problem has been in understanding the order of joins, along with the FROM. I think I understand it now when I consider it as is intended.

FROM Media.... so I am starting with media now I want to join something on to that. So I join media_genres on that ON the media id.... then I want to also JOIN on Genres so I JOIN that on genre_id...

why then is the WHERE Media_Genres.media_id? I'm not so sure. Does that take over the name as the new table? and why? not so sure but I got it!

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

Damien Watson
Damien Watson
27,419 Points
Damien Watson
Damien Watson
27,419 Points

Hey Keith, Need more info or a better example. Table names and IDs would help as well.

I am assuming you're using joins? So you have something like 3 tables, the SQL would look something like this:

SELECT * FROM media m
LEFT JOIN media_genres mg
ON m.media_id = mg.media_id
LEFT JOIN genres g
ON mg.genre_id = g.genre_id
WHERE mg.genre_id = 3
Keith Colleluori
Keith Colleluori
9,577 Points
Keith Colleluori
Keith Colleluori
9,577 Points

looks about right, I see you using aliases! Again I don't really get why the course didn't at least ask one question on a single join first before this multi part join. I'm glad I got it right finally before getting any help but not entirely sure I understand still.

For instance if it is FROM media, then why is it not WHERE media.genre_id Or vice versa.

Is the initial FROM media just an indication of where I start joining? And how do I determine then where the final WHERE statement gets its name media_genre from as opposed to media?