Page 1 of 1

Query Help - Joining 5 tables

Posted: Sun Aug 28, 2011 5:54 pm
by jabbaonthedais
Ok, I have got myself into a complicated situation where I have 5 tables and need to join them all in one mySQL query. Here is a basic outline of my table structure for this example.

Songs
ID | Song_Name

Genres
ID | Genre_Name

Groups
ID | Group_Name

Song_Genres (links songs table to genres table)
Song_ID | Genre_ID

Group_Linking (links groups table to genres table)
Genre_ID | Group_ID


I have genre "groups" where you might combine country and western into a similar group to pull together. Each song can be listed in multiple genres, and each genre can be in multiple groups. That's why i need the 2 linking tables.

Now what I am trying to do is pull all songs that are in group 1. It sounds really easy, but I am not able to wrap my head around it yet. I would appreciate any help! Am I overcomplicating this?

Thanks!

Re: Query Help - Joining 5 tables

Posted: Mon Aug 29, 2011 12:49 am
by getmizanur
select s.*, sg.genre_name, gl.group_name from group_linking gl left join song_genres sg on gl.genre_id = sg.genre_id left join songs s on s.song_id = sg.song_id left join groups g on gl.group_id = g.id left join genres gr on sg.genre_id = gr.id order by genre_name asc

try this, i have not tested the sql statement however it may be something like this.

Re: Query Help - Joining 5 tables

Posted: Sat Sep 03, 2011 1:11 pm
by ok
getmizanur: I think you forgot the WHERE clause in your SQL query.

Anyway, I would go for:

Code: Select all

SELECT
  Songs.*
FROM Songs
LEFT JOIN Song_Genres ON Song_Genres.Song_ID = Songs.ID
LEFT JOIN Group_Linking ON Group_Linking.Genre_ID = Song_Genres.Genre_ID
WHERE
  Group_Linking.Group_ID = 1
If you want also the group name and genre name you will need to query with this:

Code: Select all

SELECT
  Songs.*
FROM Songs
LEFT JOIN Song_Genres ON Song_Genres.Song_ID = Songs.ID
LEFT JOIN Group_Linking ON Group_Linking.Genre_ID = Song_Genres.Genre_ID
LEFT JOIN Groups ON Groups.ID = Group_Linking.Group_ID
LEFT JOIN Genres ON Genres.ID = Group_Linking.Genre_ID
WHERE
  Group_Linking.Group_ID = 1
I didn't test the above queries...