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
Zeb Girouard
6,505 PointsSQL Join Question Not Covered in Previous Video
I believe this question has already been asked here:
https://teamtreehouse.com/community/sql-excersice-bummering-you-need-to-specify-tables-on-the-join
...but I don't think the problem has been resolved. I'm struggling with this question because it is asking me to JOIN two tables through another one...something that wasn't shown in the previous video, except at a conceptional, diagram level.
Here is the question: https://teamtreehouse.com/library/integrating-php-with-databases/using-relational-tables/joining-tables
So I'm assuming I need two JOINs here, one to join Media to Media_Genres on their shared media_id field, then another to join Media_Genres to Genres on their shared genre_id field.
At the end, though, I'm not even sure what my deliverable is supposed to look like...should it be just the Media table columns and the Genre table columns side-by-side? I feel like this should be a little clearer in the question. Maybe even show that in the question so I know I'm getting closer.
Secondly, I keep getting a pretty unhelpful "bummer" message which just tells me "You need to specify tables ON the JOIN"...which I definitely did. Does anyone know what I'm missing?
Jason Anello
Courses Plus Student 94,610 PointsTagging Alena Holligan
The following WHERE clauses are producing the same results:
WHERE Media_Genres.media_id = Media.media_id
WHERE Media.media_id=3
The Media_Genres table only has entries for media_id = 3
2 Answers
Jason Anello
Courses Plus Student 94,610 PointsHi Zeb,
I think the answer in the other thread shouldn't be passing the challenge. It's possible that the instructions changed since then but there would be no point in having a where clause that doesn't limit the results. The WHERE clause is saying the id's have to be equal but that's already covered in the ON clauses for the joins.
The intent of the original query is to only return results where the media_id is 3 so that should be left unchanged. With the exception of adding a table to it so it won't be an ambiguous column name.
You should only add the JOIN statements to what is already there.
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;
Zeb Girouard
6,505 PointsI must have had a typo somewhere in my old code, then, because what Jason Anello typed above looks exactly like what I had, though it seems like my code didn't copy to here even though I checked the box to copy my code into my Question.... For whatever strange reason when I typed it before, it gave me the erroneous "You need to specify tables ON the JOIN" bummer message. Now when I type it in, it's all set. shrug
Anyway, thanks Jason!
Tim Stride
13,276 PointsI just had the same issue as Zeb Girouard. I'm pretty sure my code was the same as the example from Jason Anello but it kept giving that "bummer" message. Then tried various code combinations, all wrong of course. Found this answer, pasted Jason's code in and it accepted it.
Still, I'm glad that what I thought made sense actually was correct after all!
Zeb Girouard
6,505 PointsZeb Girouard
6,505 PointsApparently what I was "missing" was using the exact last line (WHERE clause) provided by @jcorum here:
SELECT * FROM Media JOIN Media_Genres ON Media.media_id = Media_Genres.media_id JOIN Genres ON Genres.genre_id = Media_Genres.genre_id WHERE Media_Genres.media_id = Media.media_id...so they didn't want media_id of 3? Why did they put that in the starter code then? I really think someone needs to look at this question from Treehouse and clarify the instructions.