How to get value from the second table if it's null in the first one-CodePudding

I have a little issue with a SQL query. So. I have three tables: Artists, Albums and Songs. Songs belong to some Album OR some Artist (only one from these two). Albums belong to only Artists. And I want to receive all recordings of songs that are in albums and that are without them with the name of the artist in each. How to do that?

Code that doesn't work:

select song_name, genre, lyrics, 
concat(first_name, ' ', last_name) as full_name, album_name
from songs 
left join artists on songs.artist_id = songs.artist_id
left join albums on artists.artist_id = albums.artist_id

Result of this code

Instead of this, I want to see the name of each artist whether he has an album or not.

CodePudding user response：

SELECT
    song_name,
    genre,
    lyrics, 
    CONCAT(first_name, ' ', last_name) as full_name,
    album_name
FROM songs
LEFT JOIN albums
       ON songs.album_id = album.id
LEFT JOIN artists
       ON songs.artist_id = artists.id OR albums.artist_id = artist.id

I'm pretty sure this'll do it!

CodePudding user response：

Have you tried to use COALESCE?

COALESCE (argument_1, argument_2, …);

If one argument is null, the next one will be shown. Use as fist argument record of table one and as second table two.