Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

PHP

Posted by John Shockey
John Shockey
45,061 Points

Stuck on this join

The question was:

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.

NOTE: You will need to add the table to the WHERE clause so that the media_id column is not ambiguous.

Here is my SQL statement:

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_Genres.media_id = 1;

I am getting the error: Bummer! Nothing needs to be removed, you need to add a JOIN for the Genres ON the genre_id column in both the Media and Genres tables.

4 Answers

jcorum
jcorum
71,816 Points

Try this: 

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

As it happens, the query doesn't need the WHERE clause in any DBMS I've ever worked in! But the editor won't accept the query without it.

P.S., it helps if you provide a link to the challenge. Note that the link is automatically provided if you click the Ask or Help button (the title seems to differ in different videos).

Samuel Jih
Samuel Jih
8,344 Points

You're right! The WHERE clause is redundant. This query also works:

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.media_id = 3

Clare A
Clare A
23,994 Points
Clare A
Clare A
23,994 Points

Hi,

Just interested in why you say the WHERE clause is redundant. Don't you need it to select the specific item in the question? (i.e. where media_id = 3) Is there a different way?

cleancoder
cleancoder
8,307 Points
cleancoder
cleancoder
8,307 Points

Thank you! This works well and I never would have come to this answer on my own and what I was doing was NOT working, so thank you again. :)

Steve Seebart
PLUS
Steve Seebart
Courses Plus Student 11,897 Points
Steve Seebart
Steve Seebart
Courses Plus Student 11,897 Points

I generally enjoy the code challenges, but when a complex answer is called for, feedback like "There's something wrong with your SQL code" is not very helpful. At least in the real world, I could run the query and get an actual error message.

Happymore Chibvura
Happymore Chibvura
1,242 Points
Happymore Chibvura
Happymore Chibvura
1,242 Points

I really agree with you Steve. My code keeps giving me the message "There's something wrong with your SQL code".

Richard Alegre
PLUS
Richard Alegre
Courses Plus Student 6,684 Points
Richard Alegre
Richard Alegre
Courses Plus Student 6,684 Points

Thank You so much!