SQL music playlist database queries from design

Question

Im trying to implement this database design. I am new to mysql and do not really understand how to connect everything so i am trying to break every query down to pieces..

Database looks like

Songs:

id title length artist_id

Artists:

id name

Playlists:

id title user_id

Playlists_Songs:

playlist_id song_id

Users:

id name email

Query - get the users playlistname and the playlist id

SELECT users.id,users.name,playlists.playlistname,playlists.id 
FROM users, playlists
WHERE users.user_id = playlists.users_id

outputs :
user_id | user_username | playlistname | id

How do i use the id from the output to continue query "deeper" to get the playlist_songs ?

Sorry for confusion question, correct me if i am wrong.

Please use the more useful explicit joins rather than implicit(comma) joins. And do google how to join 3 tables = there's lots out there. — P.Salmon, Jan 10 '19 at 16:44
Possible duplicate of [Mysql query to join three tables](https://stackoverflow.com/questions/10257433/mysql-query-to-join-three-tables) — P.Salmon, Jan 10 '19 at 16:45

score 2 · Accepted Answer · answered Jan 10 '19 at 16:44

2

To connect tables, you can use Joins. If you want to get data from playlist_songs, you can use query like

SELECT users.id,users.name,playlists.playlistname,playlists.id, playlist_songs.song_id FROM users 
        inner join playlists on users.user_id = playlists.users_id
        inner join playlist_songs on playlist_songs.playlist_id = playlists.id
WHERE users.user_id = <user_id_of_user>

You can join songs table with playlist_songs and then artists table with songs.

answered Jan 10 '19 at 16:44

Sailesh Babu Doppalapudi

1,534
1
10
22

Thank you!! Do i do the joins for the songs with playlist_songs in the same query ? – qwertyball Jan 10 '19 at 17:26
Yes you can join in same query and include the columns that you need from that tables in select statement. – Sailesh Babu Doppalapudi Jan 10 '19 at 17:36
Thank you again, i have now accomplished what i wanted! – qwertyball Jan 10 '19 at 17:45

SQL music playlist database queries from design

1 Answers1