CODEWITHSUNDEEP
postgresqlmusicbrainz
alias51
alias51

Reputation: 8608

MusicBrainz SQL query

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?

Upvotes: 1

Views: 1370

Answers (1)

user330315
user330315

Reputation:

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 = '...'

Upvotes: 2

Related Questions