CODEWITHSUNDEEP
sql
user12364297
user12364297

Reputation:

Need a select statement to join two tables that are connected by a junction table, but one has null values

I have 3 tables: Artist, Song, and song_artist (junction table).

I need to list the titles of all songs and their respective artists, even the songs without artists, but not artists without songs.

Artist table columns: artist_id, artist_name.

Song table columns: song_id, title, minutes, seconds, sales, genre_id.

song_artist table columns: song_id, artist_id

Please advise. Thanks

Upvotes: 0

Views: 38

Answers (3)

forpas
forpas

Reputation: 164089

You need a LEFT join of Song to Song_Artist and Artist:

select s.title, a.artist_name
from song s 
left join song_artist sa on sa.song_id = s.song_id
left join artist a on a.artist_id = sa.artist_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269723

If you want all songs, you want a left join. The first table should be songs:

select s.*, a.artist_name
from songs s left join
     artist_songs ars
     on ars.song_id = s.song_id left join
     artists a
     on ars.artist_id = a.artist_id;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

if you have not matching between song and song_artist then you could use LEFT JOIN 

   select a.artists, 
          s.song_id
   FROM artist a
   INNER JOIN song_artist sa ON a.artist_id = sa.artist_id
   LEFT JOIN Song s ON s.song_id = sa.song_id

Upvotes: 0

Related Questions