How to get a list of unique songs from an artist

MusicBrainz Development
, ,
1

Hi, I’m looking to link one artist to a list of single instances of their songs.

E.g.

Led Zeppelin →

  • Stairway to Heaven
  • Going to California
  • Battle of Evermore
  • Kashmir
  • Whole Lotta Love
  • …

If I go via the track table, filtered on Led Zeppelin, for Stairway to Heaven alone I get 60 odd entries:

Stairway To Heaven
Stairway to Heaven
Stairway to Heaven #4
Stairway to Heaven #5
Stairway to Heaven (5.1 mix)
Stairway to Heaven (Earls Court 1975)
Stairway to Heaven (Earl’s Court, May 25, 1975)
Stairway to Heaven (Eissporthalle, Berlin, 07/07/80 – last concert)
Stairway to Heaven (Golden Gate Trance mix)
Stairway to Heaven (Headley Granges studios, Hampshire UK, January 1971)
Stairway to Heaven (Headly Grange Studios, Hampshire, UK, Jan 71)
Stairway to Heaven (Jimmy Page instrumental)
Stairway to Heaven (Live at MSG 1973) [2018 Remaster]
Stairway to Heaven (Maple Leaf Garden, Toronto, 9/4/71)
Stairway to Heaven (Play Along)
…and so on

If I go directly via the work table, I get a more manageable 8 records:

162969 968ee3c5-21fa-35de-88f9-bd1c300ac3ee Stairway to Heaven
12841566 c088b8d4-5395-4566-a2e1-7ba8cebb78b8 Stairway to Heaven
12872748 f1d310ad-9eb2-460e-bd62-13e7535d5532 Stairway to Heaven
13015560 194893cd-e3b3-4c13-9d54-dc6c818c9304 Stairway to Heaven
13243462 e264d98b-b3d5-4bbe-92ac-df23e290dcdd Stairway to Heaven
13536534 7689a83f-dfa5-4d39-a196-9cb79820f515 Stairway to Heaven
13649361 2140b1b8-d65a-4fa2-a5b4-797f85b46fac Stairway to Heaven
13009579 ee641933-a969-4c1c-80aa-9ab24a3d370f Stairway to Heaven

However, looking at the database schema, there is no way to link between the work record and the artist record.

A query via the website however, indicates a link does exist: Search Results

Am I missing something here? How can I get the artist → song data I’m looking for?

2

I think you need to get to this information indirectly via recordings: Take all the works for which the artist in question has at least one recording.

2 Likes
3

Thanks for your reply. I’ve tried to look at the recordings table and unfortunately have run into the same problem. (See below).

Also where is the link between the work and recording? Again, the schema indicates the work table is almost standalone.

Stairway To Heaven
Stairway to Heaven
Stairway to Heaven #4
Stairway to Heaven #5
Stairway to Heaven (5.1 mix)
Stairway to Heaven (Earls Court 1975)
Stairway to Heaven (Earl’s Court, May 25, 1975)
Stairway to Heaven (Eissporthalle, Berlin, 07/07/80 – last concert)
Stairway to Heaven (Golden Gate Trance mix)
Stairway to Heaven (Headley Granges studios, Hampshire UK, January 1971)
Stairway to Heaven (Headly Grange Studios, Hampshire, UK, Jan 71)
Stairway to Heaven (Jimmy Page instrumental)
Stairway to Heaven (Maple Leaf Garden, Toronto, 9/4/71)
Stairway to Heaven (Mushroom Samba)
Stairway to Heaven (Play Along)
Stairway to Heaven (Radio 1 In Concert at the Paris Theatre, 1 April 1971)
Stairway to Heaven (Sunset Sound mix)
Stairway to Heaven (acoustic demo 1)
Stairway to Heaven (acoustic demo 2)
Stairway to Heaven (acoustic vocal demo)
Stairway to Heaven (demo)
Stairway to Heaven (end cut)
Stairway to Heaven (instrumental take 1)
Stairway to Heaven (instrumental takes 2 to 4)
Stairway to Heaven (instrumental)
Stairway to Heaven (live London 1971)
Stairway to Heaven (live at Maple Leaf Garden, Toronto, Canada September 4, 1971)
Stairway to Heaven (live, 1971-05-03: Copenhagen, Denmark)
Stairway to Heaven (newly discovered early take)
Stairway to Heaven (rehearsal take 1)
Stairway to Heaven (take 1)
Stairway to Heaven (take 1, instrumental)
Stairway to Heaven (take 2)
Stairway to Heaven (take 2, instrumental)
Stairway to Heaven (take 3, instrumental)
Stairway to Heaven (take 4, instrumental)
Stairway to Heaven (take 5, with Vocals)
Stairway to Heaven (take 6, with Vocals)
Stairway to Heaven (version 1)
Stairway to Heaven (version 2)
Stairway to Heaven (version 3)
Stairway to Heaven (version 4)
Stairway to Heaven (vocal take 1)
Stairway to Heaven (vocal take 2)
Stairway to Heaven (vocal take)
Stairway to Heaven (vocal takes 1 & 2)
Stairway to Heaven / Commericials
Stairway to Heaven / Going to California (live)
Stairway to Heaven / Going to California (live) / Rock and Roll
Stairway to Heaven / [commercials]
Stairway to Heaven Demo #1
Stairway to Heaven Demo #2
Stairway to Heaven Demo #3

4

Hi, I haven’t tinkered with musicbrainz’s database directly before, I’m only editing via musicbrainz.org website, but I think there should be a table named “l_artist_work”? might be what you’re looking for.

1 Like
5

Unfortunately no such table exists.

6

You can make a work search on the website, then use your query with the work week service.

7

Thanks for you reply. I’m really looking for a database solution for this.

The above query is just an example of what I’m looking for.

I’d like to perform this query across a large number of artists, and that would not be practical to do via the website.

8

It should, it is part of the core data. Make sure you set the search_path to include musicbrainz schema.

See the section “Relationship table structure” and “Artist-Work relationship types” for details on how l_artist_work is linking artists to their works.

1 Like
9

Note also that “Led Zeppelin” is directly credited for recordings only, not for works as the individual composers are credited instead. So you should follow the above advice too:

  • To link artist to the recordings, it goes through the artist_credit table (as shown in the schema’s overview diagram.
  • To link the recordings to the works, it goes through the l_recording_work table (using performance relationships).
4 Likes
10

Thanks for your help guys. I’ve managed to get a list of songs per artist.

I have one more problem though, sql related.

My current query for bob dylan, and led zep is:

select w.name from work w
where id in
(
select distinct entity1 from l_recording_work lrw
where entity0 in

(select id from recording r
where artist_credit in
(select artist_credit from artist_credit_name acn
where position = 0
and artist in

   (
  	select id from artist a 
  	where 
  	name like '%Bob Dylan%' or 
  	name like '%Led Zeppelin%'

   ))

)
)

This gives me a list of song names:

You Shook Me
Your Time Is Gonna Come
Black Mountain Side
Communication Breakdown
How Many More Times
Achilles Last Stand
Everything Is Broken
Ring Them Bells
Man in the Long Black Coat
Most of the Time
…and so on…

What I’d like this is each of the song names paired with the artist that recorded the song. E.g.

You Shook Me | Led Zeppelin
Your Time Is Gonna Come | Led Zeppelin
Black Mountain Side | Led Zeppelin
Communication Breakdown | Led Zeppelin
How Many More Times | Led Zeppelin
Achilles Last Stand | Led Zeppelin
Everything Is Broken | Bob Dylan
Ring Them Bells | Bob Dylan
Man in the Long Black Coat | Bob Dylan
Most of the Time | Bob Dylan
…and so on…

Any idea of what the relevant query for that might be?

11

Using a join query instead of nested select queries would allow you to do that. See a tutorial.

2 Likes
12

Thanks @yvanzo.

This works for me:

select distinct w.name as song, a.name as artist, a.id as artistid, w.id as songid from work w
right join
(
select entity0 as recording, entity1 as work from l_recording_work lrw
where entity0 in

(select id from recording r
where artist_credit in
(select artist_credit from artist_credit_name acn
where position = 0
and artist in

   (
      --relevant artist ids all in a temp table
  	select column1 from _temptable t  

   ))

)
) as link
on
w.id = link.work

left join

recording r
on
r.id = recording

left join
artist_credit_name acn
on
r.artist_credit = acn.artist_credit and acn.position = 0

left join
artist a
on
a.id = acn.artist

which gives me something like this:

song |artist |artistid|songid |
-----------------------±-------------±-------±-------+
Actions and Motives |10 Years | 230015|12844876|
Beautiful |10 Years | 230015|12844885|
So Long, Good-Bye |10 Years | 230015|12844891|
Waking Up |10 Years | 230015|12845313|
Wasteland |10 Years | 230015|12845314|
Through the Iris |10 Years | 230015|12845315|
Backlash |10 Years | 230015|12845330|
Dancing With the Dead |10 Years | 230015|12845331|
Shoot It Out |10 Years | 230015|12845333|

Cheers!

1 Like