Get artist name of work SQL Query

musicbrainz
sql
Tags: #<Tag:0x00007f076564f230> #<Tag:0x00007f076564efd8>

#1

Hi,

I am trying to get the artist name of a work using an SQL query. I managed to get the artist and the songName using this query but I don’t know how to link the recording with the work

select a.name AS artistName, ac.id as artistid, r.name AS songName
from artist_credit_name
join artist a ON artist_credit_name.artist = a.id
join artist_credit ac ON artist_credit_name.artist_credit = ac.id
join recording r ON r.artist_credit = ac.id

#2

Solved:

 select distinct a.name AS artistName, ac.id as artistid, w.name AS songName
from artist_credit_name
join artist a ON artist_credit_name.artist = a.id
join artist_credit ac ON artist_credit_name.artist_credit = ac.id
join recording r ON r.artist_credit = ac.id
JOIN l_recording_work lr ON lr.entity0=r.id
JOIN work w ON lr.entity1=w.id
where w.name = '24K Magic'

#3

Joining recording and work (through l_recording_work) like that is fine because there is only one recording-work relationship type which is named performance.

Your query finds artists credited for recordings of a work, usually they are the main performers.

If you are interested in other roles such as composers, arrangers, and so on, you can join artist and work (through l_artist_work) with link and link_type where gid is in artist-work relationship types which match your needs.