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

Posted by calvindiguidig
calvindiguidig
Courses Plus Student 4,388 Points

joining 3 table

hi guys i don't understand what the question really want to do the question said that i need to join Media table and Genre table using Media_Genre table here is my statement ''SELECT * FROM Media JOIN Genres ON Media.media_id = Genres.genre_id JOIN Media_Genres ON Media.media_id = Media_Genres.media_id WHERE Media.media_id = 3''

3 Answers

jcorum
jcorum
71,830 Points

calvin, not sure where you can up with WHERE Media.media_id = 3, and you gave no link to the challenge, but I answered a similar question several days ago and this worked.

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
calvindiguidig
calvindiguidig
Courses Plus Student 4,388 Points

thanks men for you help your query is right i never come up to idea that i need first to join Media in to Media_id i just don't understand the question here the link https://teamtreehouse.com/library/integrating-php-with-databases/using-relational-tables/joining-tables i've lost again in the code challenge lol :) thanks again men

jcorum
jcorum
71,830 Points
jcorum
jcorum
71,830 Points

calvin, I tried my code in the challenge and it worked, so I'm a bit mystified what you mean by "I'm lost again in the code challenge".

You know this line, asking for all the fields and all the columns in the Media table:

SELECT * FROM Media

This line:

JOIN Media_Genres ON Media.media_id = Media_Genres.media_id

joins the Media table to the Media_Genres table. To join one table to another you link the primary key (PK) of one table (here Media) to a foreign key (FK) of the other (here Media_Genre). Or to put it another way, you join them on the field they have in common (and if the data base is set up correctly that will be media_id, the PK in Media and a FK in Media_Genres).

This line:

JOIN Genres ON Genres.genre_id = Media_Genres.genre_id

joins Media_Genres to Genres, again by linking on the field common to both tables (genre_id), which is the PK of Genres and a foreign key of Media_Genres.

The last line:

WHERE Media_Genres.media_id = Media.media_id

isn't necessary, but the editor requires it. It's saying pick only join those records WHERE the media_ids are the same, but that's already been said!

It's too bad they didn't show a diagram of a many-to-many relationship. It would help a lot to see what the SQL is doing. But think of it this way. You can each media can have multiple genres, and each genre multiple media, so you need a join table in between (Media_Genres) to link (or relate) the two tables.

Another example would be students and classes. Since each student can take many classes and each class can have many students, you need a Student_Classes table to link them. The Student class would have a PK of student_id. The Class class would have a PK of class_id. The Student_Classes table would have two FKs, student_id and class_id. Each time a student enrolled for a class a record would be added to the Student_Classes table with the student's id (student_id) and the class's id (class_id).

If you want to practice, write a SQL statement that will join these three tables: Student, Class and StudentClasses.

calvindiguidig
PLUS
calvindiguidig
Courses Plus Student 4,388 Points
calvindiguidig
calvindiguidig
Courses Plus Student 4,388 Points

thanks for explanation don't mind about what I've said it just if i solve the challenge without any help i won in the challenge just for motivation for short hahaha :) by the way thanks for helping me to understand the relation between primary key and foreign key this is why i got confuse i just guessing which table has a foreign key thanks a lot you give more idea to combine tables i need more practice writing sql query thanks men and god bless