SQL query from relationship?

February 3, 2022

I have 2 tables in PostgreSQL – Users and Songs. I have a relationship – UsersSongs.

How can I write a SQL query to display a pair – "User.name – Song.name". Those. I want to output every song of all users. If the user has no songs, then the user should still be highlighted.

select u.email from users u
inner join users_songs us on u.id = us.user_id

I can to output just user name. How can I add the song name to this query?

How to do it on Entity Framework is clear, but I don’t know how to do it on SQL.

Thanks a lot.

>Solution :

You need to join the user and song tables via the junction table:

SELECT u.email, COALESCE(s.name, 'NA') AS song_name
FROM users u
LEFT JOIN users_songs us
    ON u.id = us.user_id
LEFT JOIN songs s
    ON s.id = us.song_id;

The above logic joins through using left joins, to ensure that users having no songs are not dropped from the query. For such users, their since record would display NA for the song name.