可以组合4个表吗？

Question

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?

Answer 1

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

<kbd>Demo Here</kbd>