0

I have this table called FEATURING:

* Artist_1,  join_phrase,  Artist_2 *

  Shakira       feat.      Rihanna  
  Eminem        feat.      Rihanna  
  Sia           feat.      Eminem  
  SeanPaul      feat.      Sia   
  Queen         feat.      DavidBowie
  LadyGaga      feat.      Beyonce   
  LadyGaga      feat.      Eminem

I need to specify a SQL query that take pairs (name of Artist_1, name of Artist_2) of artists who have never collaborated with each other but have collaborated with another artist in common

for example: X feat Y, Z feat Y ---> (X, Z) is the solution

The result it would be:

* Artist_1,  Artist_2 *

  Shakira    Eminem  
  Eminem     SeanPaul  
  Beyonce    Eminem  
  LadyGaga   Rihanna   
  Sia        Rihanna 
  Sia        LadyGaga

How I can solve? Thank you very much!

Ralph
  • 23
  • 6
  • I removed the incompatible database tags. Yes, this is solvable in SQL. You should try. – Gordon Linoff Jul 15 '17 at 13:09
  • "for example: X feat Y, Z feat Y ---> (X, Z) is the solution" -- Why (X, Z) and not (Z, X)? Your spec doesn't address this. –  Jul 15 '17 at 13:25
  • it is the same. – Ralph Jul 15 '17 at 13:28
  • This is do-able in SQL using LEFT JOINs and STRAIGHT JOINs, but possibly ugly. You're in the world of semantic triples (RDF) here. Give us your best shot and maybe someone will help more. Consider using an external tool, though, even something as simple as the Unix "join" command. –  Jul 15 '17 at 14:33
  • @barrycarter thank you for answer. I will try.. it's not simple – Ralph Jul 15 '17 at 18:25
  • Tip (untested): `SELECT * FROM FEATURING f1 JOIN FEATURING f2 WHERE (f1.Artist_1 = f2.Artist_2 OR f2.Artist_1 = f1.Artist_2) AND (f1.join_phrase = "feat." AND f2.join_phrase="feat.")` Play around with that. –  Jul 15 '17 at 18:27

2 Answers2

0

http://sqlfiddle.com/#!6/5ee966/1

Select 
  Case When t1.Artist_1 < t2.Artist_1  Then t1.Artist_1 Else t2.Artist_1 End Artist_1,
  Case When t1.Artist_1 < t2.Artist_1  Then t2.Artist_1 Else t1.Artist_1 End Artist_2
From FEATURING t1 Join FEATURING t2 On t1.Artist_2 = t2.Artist_2 and t1.Artist_1 <> t2.Artist_1

Union

Select 
  Case When t1.Artist_1 < t2.Artist_2  Then t1.Artist_1 Else t2.Artist_2 End Artist_1,
  Case When t1.Artist_1 < t2.Artist_2  Then t2.Artist_2 Else t1.Artist_1 End Artist_2
From FEATURING t1 Join FEATURING t2 On t1.Artist_2 = t2.Artist_1 

Union

Select 
  Case When t1.Artist_2 < t2.Artist_2  Then t1.Artist_2 Else t2.Artist_2 End Artist_1,
  Case When t1.Artist_2 < t2.Artist_2  Then t2.Artist_2 Else t1.Artist_2 End Artist_2
From FEATURING t1 Join FEATURING t2 On t1.Artist_1 = t2.Artist_1 and t1.Artist_2 <> t2.Artist_2

Sorted by name to remove duplications.

Leo.W
  • 499
  • 5
  • 17
  • thank you. I'll try your query. What do you think about the solution that i wrote some minute ago? – Ralph Jul 16 '17 at 11:17
0

Maybe I solved in this way but I'm not sure because the database's data are too much:

SELECT DISTINCT f1.artist_1, f2.artist_1
FROM featuring f1 join featuring f2 on (f1.artist_2 = f2.artist_2)
WHERE f1.art1 < f2.art1 
      and (f1.artist_1, f2.artist_1) not in (SELECT f3.art1, f3.art2
                                             FROM featuring f3
                                             WHERE f3.artist_1 < f3.artist_2
                                            )
order by f1.art1

The nested SELECT represents the collaborations of artists where the Artist_1 is different from Artist_2.

The main SELECT take the Artist_1 from F1 and the Artist_1 from F2.

F1 and F2 have been joined with the common artists of Artist_1 and Artist_2

Ralph
  • 23
  • 6