Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jun 2012
    Posts
    16

    Unanswered: Tricky SQL MusicDB query. Need help.

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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.)

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    Jun 2012
    Posts
    16
    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

  5. #5
    Join Date
    Jun 2012
    Posts
    16
    Just one more thing, THANKS for your help!

  6. #6
    Join Date
    Jun 2012
    Posts
    16
    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

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 06-18-12 at 21:22.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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).

  9. #9
    Join Date
    May 2008
    Posts
    277
    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.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to refer the techniques used in Relational Division, to compare sets of artists.

    Here are some examples of Relational Division.
    http://www.dbforums.com/db2/1667338-...g-listagg.html
    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.

  11. #11
    Join Date
    Jun 2012
    Posts
    16
    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

  12. #12
    Join Date
    Jun 2012
    Posts
    16
    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

  13. #13
    Join Date
    Jun 2012
    Posts
    16
    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.

  14. #14
    Join Date
    May 2008
    Posts
    277
    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.

  15. #15
    Join Date
    Jun 2012
    Posts
    16
    mosv=# select * from artist where name ='Scared Straight';
    id | name | begin_year | end_year | type | origin | gender
    -------+-----------------+------------+----------+-------+--------+--------
    82287 | Scared Straight | 1983 | 1994 | Group | 222 |
    (1 row)

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

    mosv=# select * from artist where name ='Ten Foot Pole';
    id | name | begin_year | end_year | type | origin | gender
    ------+---------------+------------+----------+-------+--------+--------
    1508 | Ten Foot Pole | 1994 | | Group | 222 |
    (1 row)

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

    After checking several examples of bands that changed their names, I found this one. The band 'Scared Straight' changed the name to 'Ten Foot Pole' and according to my database, the band exists of the same band member.... So the SQL query should at least show this one.

    I try to continue with my initial idea and your code and see if I can get it working and I try to mess around with the code from the Postgres-Forum.

    Tobias

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •