sql - 食物选择查询

Question

我有一张有以下限制的餐 table 食品

Food

Name(which has the dish name)
category (cuisine name)
DeskNo(The table that it is placed on)


Food:
Name         Category     Deskno
pasta        Mexican         1
noodles      Chines          1
friedrice    Chinese         2
Tamarindrice Indian          2

我有一个 Through 表

ThroughAway

category1
category2

ThrowAway
Category1      Category2
Indian         Chinese
Mexican        Indian

如果 category1 和 category2 放在同一个 DeskNo 上，那些盘子将被扔掉。我需要输出同一个 DeskNo 中的盘子的名称，并且两者都有可能落入 ThrowAway 的危险。需要一些帮助

Answer 1

试试这个查询

SELECT Distinct a.Name AS Dish1, b.Name As Dish2
FROM Food a, Food b, ThroughAway c
WHERE a.Name <> b.Name AND a.Deskno = b.deskno AND 
(a.category = c.category1 AND c.category2 = b.category) ;

自连接比较同一张 table 的菜和扔掉的 table

fiddle http://sqlfiddle.com/#!2/0893e6/7