mysql - 匹配相关表

Question

我有两张表，一张有名称，nametable:

id | name
---------
1  | alice
2  | bob
3  | charlie

第二个相关数据，数据表:

id | data
------------
1  | chicken
2  | fish
2  | chicken
3  | spaghetti

现在我想在nametable的两个名称之间找到匹配的匹配数据:

name1 | name2 | data
--------------------
alice | bob   | chicken

现在我有这个:

SELECT nt0.name, nt1.name, nt0.data
FROM nametable AS nt0
INNER JOIN datatable AS dt0
ON nt0.id = dt0.id
LEFT JOIN nametable AS nt1
INNER JOIN datatable AS dt1
ON nt1.id = dt1.id
WHERE dt0.data = dt1.data AND nt0.name < nt1.name;

但是这不起作用。我猜 JOIN 是原因，但我不知道还能怎么做。

Answer 1

您可以对派生表进行自联接:

SELECT
    t1.name, t2.name, t1.data
FROM (
    SELECT n.id, n.name, d.data
    FROM nametable n
    INNER JOIN datatable d
        ON d.id = n.id
) t1
INNER JOIN (
    SELECT n.id, n.name, d.data
    FROM nametable n
    INNER JOIN datatable d
        ON d.id = n.id
)t2
    ON t2.data = t1.data
    AND t2.id > t1.id