CODEWITHSUNDEEP
mysqldatabaserelational-database
qwertyball
qwertyball

Reputation: 135

SQL music playlist database queries from design

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.

Upvotes: 2

Views: 1787

Answers (1)

Sailesh Babu Doppalapudi
Sailesh Babu Doppalapudi

Reputation: 1544

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.

Upvotes: 2

Related Questions