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!
Query Help - Joining 5 tables
Moderator: General Moderators
-
jabbaonthedais
- Forum Contributor
- Posts: 127
- Joined: Wed Aug 18, 2004 12:08 pm
- getmizanur
- Forum Commoner
- Posts: 71
- Joined: Sun Sep 06, 2009 12:28 pm
Re: Query Help - Joining 5 tables
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.
try this, i have not tested the sql statement however it may be something like this.
Re: Query Help - Joining 5 tables
getmizanur: I think you forgot the WHERE clause in your SQL query.
Anyway, I would go for:
If you want also the group name and genre name you will need to query with this:
I didn't test the above queries...
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
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