MusicBrainz SQL query

Question

I am using the musicbrainz.org postgresql database, which I have installed locally and have accessed via pgAdmin.

The database is a list of music artists and associated criteria. Here is the schema.

It is possible to list various relationships that an artist might have. E.g. Here is a link to the relationships held for Madonna, which includes a link to Madonna's wikipedia page and her twitter handle amongst others.

How can I create a SQL query that would output all relationships for a given artist? I have tried:

SELECT 
  url.url, 
  artist.name
FROM 
  musicbrainz.artist, 
  musicbrainz.url, 
  musicbrainz.link
WHERE 
  url.id = artist.id
ORDER BY
  url.url ASC;

However, these links do not correlate with the correct artist. Am I using the wrong key?

According to the schema you linked to, the `url` table has no relationships at all to any other table. I can't find the `link` table. At any rate I suggest you use the explicit `JOIN` syntax when you're trying to figure out a schema of this complexity. — O. Jones, Jan 10 '15 at 14:49

score 2 · Accepted Answer · answered Jan 10 '15 at 23:06

2

I had a look at the model, and you are missing the l_artist_url table:

SELECT url.url,
       artist.name
FROM artist
  JOIN l_artist_url ON artist.id = l_artist_url.entity0
  JOIN url ON url.id = l_artist_url.entity1
WHERE artist.name = '...'
ORDER BY url.url ASC

To get the events related to an artist you would use something like this:

SELECT event.name, 
       event_type.name
FROM artist
  JOIN l_artist_event ON artist.id = l_artist_event.entity0 
  JOIN event ON event.id = l_artist_event.entity1 
  join event_type ON event_type.id = event.type 
WHERE artist.name = '...'

answered Jan 10 '15 at 23:06

Thanks, could you explain what each step in the code does for me so I can understand this answer? – alias51 Jan 11 '15 at 01:06
2

@alias51: those are just basic (inner) joins. Not much to explain really. – Jan 11 '15 at 08:05
@alias51 Query is self-explanatory. That's all about MB's data schema. URLs are saved into _url_ table, and linked to the _artist_ one via _l_artist_url_. – SuN Jan 22 '15 at 10:34

MusicBrainz SQL query

1 Answers1