sql query: list every song belonging to a particular artist, even though there is no direct foreign key reference between them

I have 4 tables

• artist (artistID, ...)
• album (albumID, artistID, ...)
• song (songID, ...)
• albumsongs (songID, albumID)

and the table that joins the many to many between album and 开发者_Python百科songs - albumsongs

Basically in my albumsongs table I have a song ID, and an album ID.

In the album table I have an artistID.

I want to list every song belonging to a particular artist, even though there is no direct foreign key reference between them. The only real link from artist to song is through album. Is that possible with some sort of advanced query magic?

Sounds like a simple a multi-table join:

select artist.name, album.title, song.title
from artist
     inner join album on album.artistid = artist.artistid
     inner join albumsongs on albumsongs.albumid = album.albumid
     inner join songs on songs.songid = albumsongs.songid
where artist.name = 'Pere Ubu'

select distinct sng.*
from song sng
inner join albumsongs a_s on a_s.song_id = sng.id
inner join album alb on alb.id = a_s.album_id
inner join artist art on art.id = alb.artist_id
where art.name like '%NAME_CRITERIA_HERE%'

or

select * from song where id in (
    select a_s.song_id
    from albumsongs a_s
    inner join album alb on alb.id = a_s.album_id
    inner join artist art on art.id = alb.artist_id
    where art.name like '%NAME_CRITERIA_HERE%'
)