We will be writing ONLY the SQL query for this challenge. The library database contains a Media table with the column

Hi John,

This is a tough one.....

This is what I had initially come up with, but it did not work:

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

Then I tried this, which also did not work:

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 = 3;

..and then finally arrived at the solution:

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

Hi John,

Can you provide a link to the challenge that you are having problems with?

In the meantime, here is an example of joining two tables together with a WHERE clause:

SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e INNER JOIN `user` AS u ON e.id = u.employee_id WHERE e.salary > 6000 AND u.user_type = 'ADMIN';

http://www.mysqltutorial.org/mysql-inner-join.aspx

Hope that helps:-)

https://teamtreehouse.com/library/integrating-php-with-databases/using-relational-tables/joining-tables

Don't you have to use the Join and where key words thanks

Oh you did use it Im stupid

SELECT media_id, title, img, format, year, category FROM Media WHERE media_id = Media INNER JOIN Genres ON join_condition1
INNER JOIN Media_Genres ON join_condition2;

Im not sure how to use a WHERE clause to make the first column not ambigous also don't understand what it is asking for in the join_condition statement1 or join_condition2

thank you Could you explain I don't understand The where clause why is WHERE Media_Genres.media_id = Media.media_id

And why is there a = in the join clauses

The = sign is there to join the media_id from the media table with the media_id from the Media_Genres table (in other words to say that they are one in the same) and like wise with the the genre_id from the Genres table as it is related to the genre_id in the Media_genre table. That's how I understand it.

As for the WHERE clause, I'm not certain as to why it needs to be there as we have already assigned the ids from the tables to their associated ids. I would imagine that we could have also stated the same for the Genres.genre_id as well. Maybe someone could explain this part a bit better than me.

I've now just signed up for this class as I'm a bit rusty with MySQL....need to refresh.

Hope this helps:-)