How to formulate this query in Postgres without using views

Question

I have this exercise query that I'm trying to formulate, which also happens to be quite tricky because the exercise explicitly tells you not to use views. It's based on the Musicbrainz database schema, but the only relations that are used, stripped of all the unused attributes, are:

label {id(PK), name}

release {id(PK), name}

-- associates a label to all its releases
release_label {id(PK), release(FK), label(FK)}

The query is: "list all the pairs of records labels that never released any common release BUT published a release in collaboration with the same third record label (show the names of the pairs of record labels)".

I've been trying to wrap my head around this query for days, but I only solved the first half (pairs of labels that never released anything in common) using some self joins on the same table to create the pairs

SELECT DISTINCT label_1.name, label_2.name
FROM label label_1 JOIN label label_2 ON label_1.name < label_2.name
WHERE NOT EXISTS 
     (SELECT * 
      FROM release release_A 
      JOIN release_label RLA ON release_A.id = RLA.release
      JOIN label label_A ON RLA.label = label_A.id
      JOIN release release_B ON release_A.name = release_B.name
      JOIN release_label RLB ON release_B.id = RLB.release
      JOIN label label_B ON RLB.label = label_B.id
      WHERE label_1.name = label_A.name AND label_2.name = label_B.name
     )

Basically the outer query creates every pair of record labels in order and each pair gets selected using a correlated subquery that search if there isn't any common release between those two record labels. This part works as intended, but I have no idea how to find the same third label without using any views or any conditional or control structures. Help.

Answer 1

Consider a third self join of label with two added EXISTS clauses differing in their WHERE conditions:

SELECT DISTINCT label_1.name, label_2.name, label_3.name
FROM label label_1
JOIN label label_2 ON label_1.name < label_2.name
JOIN label label_3 ON label_3.name <> label_1.name
                   AND label_3.name <> label_2.name
WHERE NOT EXISTS 
     (SELECT 1
      FROM release release_A 
      JOIN release_label RLA ON release_A.id = RLA.release
      JOIN label label_A ON RLA.label = label_A.id
      JOIN release release_B ON release_A.name = release_B.name
      JOIN release_label RLB ON release_B.id = RLB.release
      JOIN label label_B ON RLB.label = label_B.id
      WHERE label_1.name = label_A.name
        AND label_2.name = label_B.name
     )
 AND EXISTS
     (SELECT 1
      FROM release release_A 
      JOIN release_label RLA ON release_A.id = RLA.release
      JOIN label label_A ON RLA.label = label_A.id
      JOIN release release_B ON release_A.name = release_B.name
      JOIN release_label RLB ON release_B.id = RLB.release
      JOIN label label_B ON RLB.label = label_B.id
      WHERE label_1.name = label_A.name 
        AND label_3.name = label_B.name
     )
 AND EXISTS
     (SELECT 1
      FROM release release_A 
      JOIN release_label RLA ON release_A.id = RLA.release
      JOIN label label_A ON RLA.label = label_A.id
      JOIN release release_B ON release_A.name = release_B.name
      JOIN release_label RLB ON release_B.id = RLB.release
      JOIN label label_B ON RLB.label = label_B.id
      WHERE label_2.name = label_A.name 
        AND label_3.name = label_B.name
     )