Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    16

    Unanswered: Problems with a somewhat tricky SQL query.

    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
    Sorry!
    I have moved my post to ANSI SQL forum.
    Last edited by tonkuma; 06-18-12 at 14:38.

  3. #3
    Join Date
    Jan 2012
    Posts
    84
    One of possible solutions could be to aggregate band members, convert them to strings and compare these strings to identify bands with the same set of members.
    Here is a little example:
    Code:
    create table artist_relation as
    select 1 artist0, 101 artist1, 'member of' as type union
    select 2, 101, 'member of' union
    select 3, 102, 'member of' union
    select 4, 102, 'member of' union
    select 5, 102, 'member of' union
    select 6, 102, 'member of' union
    select 5, 103, 'member of' union
    select 6, 103, 'member of' union
    select 7, 103, 'member of' union
    select 5, 104, 'member of' union
    select 6, 104, 'member of' union
    select 7, 104, 'member of' union
    select 5, 105, 'member of' union
    select 6, 105, 'member of' union
    select 8, 105, 'member of' union
    select 5, 106, 'member of' union
    select 6, 106, 'member of' union
    select 9, 106, 'member of'  
    ;
    Code:
    select * from artist_relation;
    
     artist0 | artist1 |   type    
    ---------+---------+-----------
           6 |     105 | member of
           2 |     101 | member of
           1 |     101 | member of
           4 |     102 | member of
           6 |     103 | member of
           5 |     104 | member of
           6 |     102 | member of
           6 |     106 | member of
           7 |     104 | member of
           5 |     105 | member of
           8 |     105 | member of
           9 |     106 | member of
           5 |     103 | member of
           6 |     104 | member of
           3 |     102 | member of
           5 |     106 | member of
           7 |     103 | member of
           5 |     102 | member of
    Notice that bands 103 and 104 have the same members id 5, 6, 7.

    This is a query to identify equal sets of members from the above table:
    Code:
    WITH src AS(
    	SELECT artist1, array_to_string(array_agg(artist0 ORDER BY artist0), ',' ) members
    	FROM artist_relation ar
    	WHERE ar.type = 'member of'
    	GROUP BY artist1
    )
    SELECT * 
    FROM src a1
    JOIN src a2 ON a1.members = a2.members AND a1.artist1 <> a2.artist1
    ;
    
    
     artist1 | members | artist1 | members 
    ---------+---------+---------+---------
         103 | 5,6,7   |     104 | 5,6,7
         104 | 5,6,7   |     103 | 5,6,7
    A result contains duplicates - all possible combinations of "equal" bands,
    to get rid of these duplicates the query must be slightly modified:
    Code:
    WITH src AS(
    	SELECT artist1, array_to_string(array_agg(artist0 ORDER BY artist0), ',' ) members
    	FROM artist_relation ar
    	WHERE ar.type = 'member of'
    	GROUP BY artist1
    )
    SELECT * 
    FROM src a1
    JOIN src a2 ON a1.members = a2.members AND a1.artist1 > a2.artist1
    ;
    
    
     artist1 | members | artist1 | members 
    ---------+---------+---------+---------
         104 | 5,6,7   |     103 | 5,6,7

  4. #4
    Join Date
    Jun 2012
    Posts
    16
    Hi there,

    thanks for your reply. I tried your SQL-query on my system and this is what I get:

    mosv=# WITH src AS(
    SELECT artist1, array_to_string(array_agg(artist0 ORDER BY artist0), ',' ) members
    FROM artist_relation ar
    WHERE ar.type = 'member of'
    GROUP BY artist1
    )
    SELECT *
    FROM src a1
    JOIN src a2 ON a1.members = a2.members AND a1.artist1 > a2.artist1
    ;
    artist1 | members | artist1 | members
    ---------+---------+---------+---------
    (0 rows)

    not sure what is going wrong...

    do you have an idea?

    Tobias

  5. #5
    Join Date
    Jun 2012
    Posts
    16
    Hello Kordirko,

    thank you very much! You pretty much solved my problem... After I removed the WHERE clause it was working just fine. I got roughly 500 artists/bands that changed their names. All the samples that I checked gave back the correct answer.... Now the only thing left for me to do, is to get a JOIN in your SQL query so that it gives out the actual names from the ARTIST table.

    Kordirko, thanks again for your help!

    Tobias

  6. #6
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by timeout2575 View Post
    to get a JOIN in your SQL query so that it gives out the actual names from the ARTIST table.
    Use dependent subqueries to retrieve names
    Code:
    SELECT
       a1.*,  
       ( select name from artist A where A.id = A1.artist0 ) a1_name,   
       a2.*,
       ( select name from artist A where A.id = A2.artist0 ) a2_name,
    FROM src a1
    JOIN src a2 ON a1.members = a2.members AND a1.artist1 > a2.artist1

Posting Permissions

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