postgreSQL - 自连接以查找两个标签，其版本与第三个标签相同

Question

我正在使用PostgreSQL和PgAdmin 4，并且正在使用MusicBrainz数据库。 我需要找到一对从未共同发布过版本的唱片公司，但他们都发布了带有第三个标签的版本(两者的标签相同(。

数据库中有以下表： 标签(ID，名称..( ID 是主键。 release_label(id、版本、标签(id 是主键和标签外键。

我已经尝试过自加入，但它不起作用：

SELECT l1.name as label_1 , l2.name as label_2
FROM release_label as r1 INNER JOIN label as l1 ON r1.label=l1.id, label as l2
INNER JOIN (release_label as r2 LEFT JOIN release_label as r3
ON r3.label=r2.label)ON r2.label=l2.id WHERE r1.release != r2.release 
AND r1.label!= r3.label AND r1.release=r3.release
GROUP BY label_1,label_2 ORDER BY label_1,label_2

谢谢建议。

Answer 1

此查询获取从未发布任何共同点的标签对：

select l1.id as id1, l2.id as id2
from label l1 cross join
label l2 left join
release_label rl1 
on l1.id = rl1.label left join
release_label rl2
on l2.id = rl2.label and rl2.release = rl1.release
where rl1.label is null and l1.id < l2.id;

现在，你想要第三个标签，它已经与两者一起发布。

select ll.*, rl3_1.label as in_common
from (select l1.id as id1, l2.id as id2
from label l1 cross join
label l2 left join
release_label rl1 
on l1.id = rl1.label left join
release_label rl2
on l2.id = rl2.label and rl2.release = rl1.release
where rl1.label is null and l1.id < l2.id
) ll join
release_label rl1
on rl1.label = ll.id1 join
release_label rl2
on rl2.label = ll.id2 join
release_label rl3_1
on rl3_1.release = rl1.release join 
release_label rl3_2
on rl3_2.release = rl2.release and
rl3_2.label = rl3_1.label;

编辑：

另一种方法可能更简单：

select l1.id, l2.id, l3.id as in_common_id
from label l1 join
label l2
on l1.id < l2.id join
label l3
on l1.id <> l3.id and l2.id <> l3.id
where -- have no releases in common
not exists (select 1
from release_label rl1 join
release_label rl2
on rl1.release = rl2.release
where rl1.label = l1.id and rl2.label = l2.id
) and
-- l1 has a release with l3
exists (select 1
from release_label rl1 join
release_label rl3
on rl1.release = rl3.release
where rl1.label = l1.id and rl3.label = l3.id
) and
-- l2 has a release with l3
exists (select 1
from release_label rl2 join
release_label rl3
on rl2.release = rl3.release
where rl2.label = l2.id and rl3.label = l3.id
);

from子句生成标签的所有候选行程。exists将检查要检查的三个条件。 这是我将使用的版本，因为我认为逻辑更容易遵循。

在这些查询中的任何一个中，您都可以(当然(在前两个 id 上使用select distinct来获取您要查找的对。