Tricky SQL MusicDB query. Need help.

timeout2575 · #1 (**permalink**) 06-18-12, 09:08

I am new to this page and I hope that my post is in the right section. Anyway, I need some help with a query.

This is the table:

artist_relation

artist0_/artist1_/___type____/founder/begin_year/end_year/
285850/1352__/member of__/______/__1974__/__1980____
290121/1352__/member of_/___Y___/__1973__/________
290121/316942/_sibling___/_______/________/_____

this is the second table that I need for my query.

artist

Table: Artist

___id___ / ___name___ /_begin_year_/_end_year_/__type__/_origin_/gender

__938___ /Clapton,Eric/___1945____/___________/_Person_/__221___/__M___
__1352__ /__AC/DC___/___1973____/___________/_Group__/___13___/______
_285850_ /Scott,Bon___/___1946____/__1980_____/_Person_/__221___/__M___

Here comes the query:

There are some bands, that have changed the bandname, but the band members stayed the same. Give out the bandnames (as pairs) of bands that have changed the name and apart from that stayed the same. (Apparently it doesnt play a role, when they have played for the band)

My idea:

Look up the "artist0"s that make up "artist1" and than search if that exact combination is found in another "artist1"... but that is far beyond my skills...

Any ideas?

Thanks, Tobias

Let me rephrase it:

Well like I said, a couple of ARTIST0 make up ARTIST1 and now we want to know, when the same set of ARTIST0 make up a different ARTIST1.

This is a stupid example. 4 ARTIST0 make up the Band 'ABBA', they all have numbers 33544, 240614, 128193. 240608 and they make up ARTIST1 in this case ABBA and ABBA has the number 910. Now I want to know if, that set of ARTIST0 make up a different ARTIST1 and if this is the case, than I need an output that lists them as pairs. In this case the band name ABBA next to the "new" band name... Maybe this was a better explanation...

tonkuma · #2 (**permalink**) 06-18-12, 13:38

Your sample data are too little and incomplete.

For example, some of my interpretations are...
I saw a Group 'AC/DC'(id is 1352, begin at 1973)
The members are...
id = 285850(begin at 1974, end at 1980)
id = 290121(founder, begin at 1974, not ended)

id = 290121 is a sibling of id = 316942

But, I couldn't see any information for id = 290121 nor id = 316942 in Artist table.

Another my hope is...
please supply informations of Band 'ABBA' and members 33544, 240614, 128193. 240608, by the form of rows in artist_relation table and artist table.

Finally,
please publish your required results from the publushed data of tables
(including additional rows to make them complete and sufficient to show your requirements.)

tonkuma · #3 (**permalink**) 06-18-12, 13:47

Here is a sample data including my understandings from your literal explanations.
Would you supplement the data(or remove some data, if not neccesary)

Note: Made on DB2 9.7.5 for Windows.

Code:

------------------------------ Commands Entered ------------------------------
WITH
  artist_relation
( artist0 , artist1 , type , founder , begin_year , end_year ) AS (
VALUES
  ( 285850 ,   1352 , 'member of' , null , 1974 , 1980 )
, ( 290121 ,   1352 , 'member of' , 'Y'  , 1973 , null )
, ( 290121 , 316942 , 'sibling'   , null , null , null )
, (  33544 ,    910 , 'member of' , null , null , null )
, ( 240614 ,    910 , 'member of' , null , null , null )
, ( 128193 ,    910 , 'member of' , null , null , null )
, ( 240608 ,    910 , 'member of' , null , null , null )
)
, artist
( id , name , begin_year , end_year , type , origin , gender ) AS (
VALUES
  (    938 , 'Clapton,Eric' , 1945 , null , 'Person' ,  221 , 'M'  )
, (   1352 , 'AC/DC'        , 1973 , null , 'Group'  ,   13 , null )
, ( 285850 , 'Scott,Bon'    , 1946 , 1980 , 'Person' ,  221 , 'M'  )
, (    910 , 'ABBA'         , null , null , 'Group'  , null , null )
, ( 290121 , null           , null , null , 'Person' , null , null )
, ( 316942 , null           , null , null , 'Person' , null , null )
, (  33544 , null           , null , null , 'Person' , null , null )
, ( 240614 , null           , null , null , 'Person' , null , null )
, ( 128193 , null           , null , null , 'Person' , null , null )
, ( 240608 , null           , null , null , 'Person' , null , null )
)
SELECT * FROM artist_relation;
------------------------------------------------------------------------------

ARTIST0     ARTIST1     TYPE      FOUNDER BEGIN_YEAR  END_YEAR   
----------- ----------- --------- ------- ----------- -----------
     285850        1352 member of -              1974        1980
     290121        1352 member of Y              1973           -
     290121      316942 sibling   -                 -           -
      33544         910 member of -                 -           -
     240614         910 member of -                 -           -
     128193         910 member of -                 -           -
     240608         910 member of -                 -           -

  7 record(s) selected.

Code:

SELECT * FROM artist;
------------------------------------------------------------------------------

ID          NAME         BEGIN_YEAR  END_YEAR    TYPE   ORIGIN      GENDER
----------- ------------ ----------- ----------- ------ ----------- ------
        938 Clapton,Eric        1945           - Person         221 M     
       1352 AC/DC               1973           - Group           13 -     
     285850 Scott,Bon           1946        1980 Person         221 M     
        910 ABBA                   -           - Group            - -     
     290121 -                      -           - Person           - -     
     316942 -                      -           - Person           - -     
      33544 -                      -           - Person           - -     
     240614 -                      -           - Person           - -     
     128193 -                      -           - Person           - -     
     240608 -                      -           - Person           - -     

  10 record(s) selected.

timeout2575 · #4 (**permalink**) 06-18-12, 15:52

The University of Heidelberg provided us with sample tables from a database ( MusicBrainz - The Open Music Encyclopedia ). Each sample had three tuples. The two tables that are needed for my query is the table ARTIST and the table ARTIST_RELATION. The tables you created are correct. I added as an example ABBA to the table. I could look up, the names for every member of ABBA, but I seems irrelevant for the query. What I was trying to say is the following:

artist0 = 33544 and artist0 = 240614 and artist0 = 128193 and artist0 = 240608 together make up artist1 = 910 (910 = ABBA). Now, I want to know, if that UNIQUE combination of artist0s make up another artist1. If this is the case, I want the name of the 'old' band next to the 'new' band name.

Like I said, your tables are correct... and we didnt get more information than that. Does that help? If you want, I can sent you the PDF with the information we got.

Cheers,
Tobias

timeout2575 · #5 (**permalink**) 06-18-12, 15:55

Just one more thing, THANKS for your help!

timeout2575 · #6 (**permalink**) 06-18-12, 17:56

This is the query that we came up with so far:

SELECT m.artist1, m.relatedartist FROM (SELECT ar1.artist1, ar2.artist1 AS relatedartist, COUNT(DISTINCT ar1.artist0) AS cnt FROM artist_relation ar1 INNER JOIN artist_relation ar2 ON ar2.artist0 = ar1.artist0 AND ar2.artist1 <> ar1.artist1 GROUP BY ar1.artist1 , ar2.artist1) m JOIN (SELECT artist1, COUNT(DISTINCT artist0) AS cnt FROM artist_relation GROUP BY artist1) ar ON ar.artist1 = m.artist1 AND ar.cnt = m.cnt;

This is are the first couple of tuples from the result:

artist1 | relatedartist
---------+---------------
4 | 115885
44 | 35513
45 | 57
45 | 1070
48 | 123675
48 | 762446
66 | 232997
141 | 51630
213 | 56878
213 | 81386
227 | 47537
318 | 317
318 | 12734
318 | 108711
320 | 147783
375 | 743338
418 | 2361
418 | 54346

The list goes on and the result has some BIG flaws

1.) The result should be a name, not the number from artist1.
2.) If you control the results, than you discover the following problem.

Let s have a look at artist1 with the number 45.

mosv=# select * from artist_relation where artist1 = '45';
artist0 | artist1 | type | founder | begin_year | end_year
---------+---------+----------------+---------+------------+----------
280675 | 45 | member of band | | |
(1 row)

mosv=# select * from artist_relation where artist1 = '57';
artist0 | artist1 | type | founder | begin_year | end_year
---------+---------+----------------+---------+------------+----------
280676 | 57 | member of band | | 1994 | 2006
280675 | 57 | member of band | | 1994 | 2006
280677 | 57 | member of band | | 1996 | 2006
(3 rows)

My understanding is, that artist1 (band) = 45 had one artist0 (one bandmember) with the number 280675. If you have a look at the related band 57, you find the artist0 number there as well, but with TWO other artist0 and the query should only give out the artist1 where the band members stayed the same.

Not sure, where the error is.

By the way, the right results are among them, if you check artist1 = 45 to related artist 1070, this solo singer just changed her name, but like I said, at one point that singer was part of a group and that result should not have been given out.

Here is the query, that proves that point.

mosv=# select * from artist_relation where artist1 = '1070';
artist0 | artist1 | type | founder | begin_year | end_year
---------+---------+----------------+---------+------------+----------
280675 | 1070 | member of band | | |
(1 row)

Maybe someone can help me.

Thanks in advance.

Tobias

tonkuma · #7 (**permalink**) 06-18-12, 20:15

I thought that you didn't understand what I wrote.

I requested you to describe your issue by supplying sample/test data and expected results from the data.
I showed first part(i.e. sample/test data).
I hope you to add/modify the data and to publish expected results from the data.

The reasons are...
(1) It is hard for me to understand the requirements completely by reading long descriptions and following the links.

(2) It is better to test sample SQL statement before publish on forum.
And, it is neccesary data(tables) to test the SQL statement.
Who would be a person well suited for preparing test data?

Of cause, you are free how to describe your issue.
But, if you want useful answer/hint from more peoples, it would be better to follow my advice.

I hope you to publish sample/test data by one of the following ways.
(a) Using common-table-expressions, like I showed.
WITH artist_relation(...) AS (...) , artist(...) AS (...)

(b) CREATE TABLE statements and INSERT INTO statements.

tonkuma · #8 (**permalink**) 06-19-12, 02:38

Here are updated sample/test data including additional rows guessed from the information in your last post.

Code:

SELECT * FROM artist_relation;
------------------------------------------------------------------------------

ARTIST0     ARTIST1     TYPE      FOUNDER BEGIN_YEAR  END_YEAR   
----------- ----------- --------- ------- ----------- -----------
     285850        1352 member of -              1974        1980
     290121        1352 member of Y              1973           -
     290121      316942 sibling   -                 -           -
      33544         910 member of -                 -           -
     240614         910 member of -                 -           -
     128193         910 member of -                 -           -
     240608         910 member of -                 -           -
     280675          45 member of -                 -           -
     280676          57 member of -              1994        2006
     280675          57 member of -              1994        2006
     280677          57 member of -              1996        2006
     280675        1070 member of -                 -           -

  12 record(s) selected.

Note: I changed values of type(of additional rows) to 'member of'. Because, it was used in your original post.

Code:

SELECT * FROM artist;
------------------------------------------------------------------------------

ID          NAME         BEGIN_YEAR  END_YEAR    TYPE   ORIGIN      GENDER
----------- ------------ ----------- ----------- ------ ----------- ------
        938 Clapton,Eric        1945           - Person         221 M     
       1352 AC/DC               1973           - Group           13 -     
     285850 Scott,Bon           1946        1980 Person         221 M     
        910 ABBA                   -           - Group            - -     
     290121 -                      -           - Person           - -     
     316942 -                      -           - Person           - -     
      33544 -                      -           - Person           - -     
     240614 -                      -           - Person           - -     
     128193 -                      -           - Person           - -     
     240608 -                      -           - Person           - -     
         45 -                      -           - Group            - -     
     280675 -                      -           - Person           - -     
         57 -                      -           - Group            - -     
     280676 -                      -           - Person           - -     
     280675 -                      -           - Person           - -     
     280677 -                      -           - Person           - -     
       1070 -                      -           - Group            - -     

  17 record(s) selected.

(1) Please fill/modify values of columns, and/or add/remove rows, if they were necessary.
For example, I guessed if 45 might be in origin of 1070. But, I'm not sure, then I leave it to null.

Code:

ID          NAME         BEGIN_YEAR  END_YEAR    TYPE   ORIGIN      GENDER
----------- ------------ ----------- ----------- ------ ----------- ------
...
       1070 -                      -           - Group           45 -

(2) Please publish your required output from the data.
Long descriptions are not necessary for me(although, some other peoples like them).

futurity · #9 (**permalink**) 06-19-12, 11:33

What's tricky is that you need to compare unique sets of artists with each other to determine bands with the same composition of artists. I don't know that there's a standard way of doing that, but since you double-posted this in the PostgreSQL forums, I assume that's the DBMS you're using. In that case, the array function will give you what you're looking for:

Code:

select
    artist1 as group_id,
    array(
        select artist0
        from artist_relation as ar2
        where ar2.artist1 = ar1.artist1
        order by artist0
    ) as member_ids
from artist_relation as ar1
group by artist1

This looks like a homework assignment, so I'm not going to give you the full answer. But using the results of the above query, along with the appropriate aggregation function(s), you should be able to pick out bands where a set of artists occurs more than once.

tonkuma · #10 (**permalink**) 06-19-12, 14:23

You may want to refer the techniques used in Relational Division, to compare sets of artists.

Here are some examples of Relational Division.
Relational Division using LISTAGG
You will see basically 3 techniques for Relational Division in the thread.
Call them temporarily ...
- Nested NOT EXISTS
- JOIN - HAVING - COUNT
- LISTAGG

Although there might be some differences in syntax and function names,
these techniques would be used on most RDBMS.

timeout2575 · #11 (**permalink**) 06-19-12, 16:13

Thanks for all the comments so far.

This is indeed a homework assignment from Heidelberg University. I am new to databanks, this is my third school week, and I gave up on this query after trying to solve it for several hours. That is why I looked for help on here.

Hopefully, I can solve it now. Again, thanks to everyone.

Tobias

timeout2575 · #12 (**permalink**) 06-20-12, 09:02

I am stuck again...

I used your code...

mosv=# select
mosv-# artist1 as group_id,
mosv-# array(
mosv(# select artist0
mosv(# from artist_relation as ar2
mosv(# where ar2.artist1 = ar1.artist1
mosv(# order by artist0
mosv(# ) as member_ids
mosv-# from artist_relation as ar1
mosv-# group by artist1
mosv-# ;

and saw what happened.... it lists all the band members...

251 | {237295,238442,238442,239217,485586,635016}
21370 | {236949,236952,236954,295964}
2026 | {322779}
7494 | {513496,632197}
436078 | {453168}
300989 | {559477,559481}
515915 | {196}
12310 | {65461,403404}
613071 | {127542,338259}
14585 | {9131,309746,322699,350502}
823426 | {15612,47477}
114021 | {349897,661406}
317524 | {471939}
110079 | {488200}

Once again, it basically lists all the members of artist1 (bands) in an array.

I had the following idea for the next step.

I want to duplicate that code and give the array names, than I want to compare array 1 with array 2, as in array1.members_ids = array2.members_ids and array1.artist1 != array2.artist1;

Is that possible? Can that (or something similiar) be done with arrays?

Thanks again to everyone... !

Tobias

timeout2575 · #13 (**permalink**) 06-20-12, 09:46

but that idea backfired... apparently there is no such thing as "Tupelvariablen" for arrays... and you cant (or at least I cant) compare the "ALIAS" statements... Argh..... And the notes from the university are good for nothing.... bugger.

futurity · #14 (**permalink**) 06-20-12, 10:36

The solution posted in the PostgreSQL forums will work. It basically does the same thing I was hinting at, except it uses the array_agg() function, which was introduced in newer version of PostgreSQL, instead of my method. However, my method may allow for more complex expressions, so you can, for example, join on the artist table and use artist names instead of id numbers.

You can compare arrays directly (you don't need to use array_to_string()), so what you're proposing will work. However, you need to make sure the arrays are ordered the same to catch duplicates, so you need to use the ORDER BY clause.

If you're not getting any results, as it appears you aren't in the other thread, then that means there are no duplicates. Looking at the sample data you posted -- if that's what you're actually working with -- this appears to be the case.

timeout2575 · #15 (**permalink**) 06-20-12, 11:20

Internet Services

Web Development

Digital Marketing

Consumer Tech