Query Help - Joining 5 tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

Query Help - Joining 5 tables

Post 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!
User avatar
getmizanur
Forum Commoner
Posts: 71
Joined: Sun Sep 06, 2009 12:28 pm

Re: Query Help - Joining 5 tables

Post 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.
User avatar
ok
Forum Contributor
Posts: 393
Joined: Wed May 31, 2006 9:20 am
Location: The Holy Land

Re: Query Help - Joining 5 tables

Post 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...
Post Reply