Syaiful Amri Syaiful Amri - 4 months ago 16
PHP Question

Is it possible to combine 4 tables?

I need to query data from 4 tables. I'm not sure this is the best db design to approach this. This is simplified version. The actual table have more column.I have table for users who upload their song:

Id | Username |
---------------
1 | John |
2 | Michael |
3 | Frank |


Then I have song table like this

Id | Title | UserId
----------------------
1 | Title 1 | 1
2 | Title 2 | 1
3 | Title 3 | 2
4 | Title 4 | 2


Then a playlist table like this

Id | Title | UserId
------------------------
1 | My Playlist | 3


Then a playlist entries where a user save song id belong to a playlist

Id | SongId | PlaylistId
--------------------------
1 | 2 | 1
2 | 4 | 1
3 | 3 | 1


What I want to achieve after query is something like this:

Playlist | By | Song List
------------------------------------------------------------
My Playlist | Frank | Title 2 - John, Title 3 - Michael,
| | Title 4 - Michael
-------------------------------------------------------------


Any idea how to query this in MySQL. Or any better table design to achieve sane result?

JPG JPG
Answer

Try this:

select
    p.Title as Playlist,
    u1.Username as `By`,
    group_concat(s.Title, ' - ', u2.Username order by s.Id) as `Song List`
from playlist p
join playlist_entries pe
on p.Id= pe.PlaylistId
join song s
on pe.SongId = s.Id
join user u1
on p.UserId = u1.Id
join user u2
on s.UserId = u2.Id

Demo Here