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 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?

Answer

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'