mysql - 在一个表中查找其他两个表中的所有行

Question

我正在为如何为此查询而苦苦挣扎。我有三张表……配料、食谱和商店。我正在尝试构建一个查询，该查询将告诉我可以使用商店提供的原料制作哪些食谱。我的表是:

    mysql> SELECT * FROM ingredients;    +---------+    | id      |    +---------+    | apple   |    | beef    |    | cheese  |    | chicken |    | eggs    |    | flour   |    | milk    |    | pasta   |    | sugar   |    | tomato  |    +---------+    10 rows in set (0.00 sec)    mysql> SELECT * FROM stores;    +----------+------------+    | name     | ingredient |    +----------+------------+    | target   | apple      |    | target   | chicken    |    | target   | flour      |    | target   | milk       |    | target   | sugar      |    | wal-mart | beef       |    | wal-mart | cheese     |    | wal-mart | flour      |    | wal-mart | milk       |    | wal-mart | pasta      |    | wal-mart | tomato     |    +----------+------------+    11 rows in set (0.00 sec)    mysql> SELECT * FROM recipes;    +---------------+------------+    | name          | ingredient |    +---------------+------------+    | apple pie     | apple      |    | apple pie     | flour      |    | apple pie     | milk       |    | apple pie     | sugar      |    | cheeseburger  | beef       |    | cheeseburger  | cheese     |    | cheeseburger  | flour      |    | cheeseburger  | milk       |    | fried chicken | chicken    |    | fried chicken | flour      |    | spaghetti     | beef       |    | spaghetti     | pasta      |    | spaghetti     | tomato     |    +---------------+------------+    13 rows in set (0.00 sec)    mysql>

考虑到上述情况，我想构建一个查询，在其中我给它提供商店名称(在这个例子中说沃尔玛)，它会生成一个食谱列表，其中包含我可以使用沃尔玛可用的原料制作的食谱(芝士汉堡和意大利面)。

这是创建这些表的 SQL:

    CREATE TABLE IF NOT EXISTS ingredients (      id varchar(32) NOT NULL,      PRIMARY KEY (id)    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;    INSERT INTO ingredients (id) VALUES    ('apple'),    ('beef'),    ('cheese'),    ('chicken'),    ('eggs'),    ('flour'),    ('milk'),    ('pasta'),    ('sugar'),    ('tomato');    CREATE TABLE IF NOT EXISTS recipes (      `name` varchar(32) NOT NULL,      ingredient varchar(32) NOT NULL,      PRIMARY KEY (`name`,ingredient)    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;    INSERT INTO recipes (`name`, ingredient) VALUES    ('apple pie', 'apple'),    ('apple pie', 'flour'),    ('apple pie', 'milk'),    ('apple pie', 'sugar'),    ('cheeseburger', 'beef'),    ('cheeseburger', 'cheese'),    ('cheeseburger', 'flour'),    ('cheeseburger', 'milk'),    ('fried chicken', 'chicken'),    ('fried chicken', 'flour'),    ('spaghetti', 'beef'),    ('spaghetti', 'pasta'),    ('spaghetti', 'tomato');    CREATE TABLE IF NOT EXISTS stores (      `name` varchar(32) NOT NULL,      ingredient varchar(32) NOT NULL,      UNIQUE KEY NAME_INGREDIENT (`name`,ingredient)    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;    INSERT INTO stores (`name`, ingredient) VALUES    ('target', 'apple'),    ('target', 'chicken'),    ('target', 'flour'),    ('target', 'milk'),    ('target', 'sugar'),    ('wal-mart', 'beef'),    ('wal-mart', 'cheese'),    ('wal-mart', 'flour'),    ('wal-mart', 'milk'),    ('wal-mart', 'pasta'),    ('wal-mart', 'tomato');

Answer 1

试试这个:

SELECT r.name FROM recipes r          
GROUP BY r.name
HAVING COUNT(*) = (SELECT COUNT(*) 
     FROM recipes r2 INNER JOIN stores s 
        ON r2.ingredient = s.ingredient AND s.name = 'wal-mart'
     WHERE r.name = r2.name)

fiddle demo