mysql - SQL查询从多个表中选择相同的字段

Question

我在架构中定义了三个表

donuts (name: string, price: integer) 
grocery (no: string, gname: string, minCredit: integer) 
distributor (dname: string, gno: string, deliverydate: date)

distributor.dname和distributor.gno是引用 donut 和杂货的外键，其中的键是名称而不是各自的我在尝试编写的2个查询时遇到了问题:

我正在尝试展示可以在同一家杂货店找到的成对 donut (即所有携带一个 donut 的商店都被另一个商店拿走，反之亦然)。输出模式应该是 (donutname1, donutname2)

我尝试过的是:

select d.name as donutname1, d.name as donutname2
from donuts d, grocery g, distributor dd
where d.name IN  
    (select  d.name 
     from donuts d, grocery g, distributor dd 
     where d.name = dd.name)

这似乎是错误的，并且似乎给了我错误的结果。我如何查询两个名字并获得结果？

Answer 1

尝试使用您的数据进行以下实验:

SELECT aa.name AS donutname1, bb.name AS donutname2
FROM dunuts AS aa
INNER JOIN donuts AS bb
ON aa.name = bb.name
INNER JOIN distributor AS cc
ON aa.name = cc.dname
INNER JOIN distributor AS dd
ON bb.name = cc.dname
WHERE cc.gno = dd.gno;