mysql - 如何让查询显示出来？

Question

我有3张 table

recipe
+----------+---------+
| recipe_id|     name|
+----------+---------+
|         1|   name_1|
+----------+---------+
|         2|   name_2|
+----------+---------+
|         3|   name_3|
+----------+---------+

ingredient
+--------------+---------+
| ingredient_id|     name|
+--------------+---------+
|             7|   cheese|
+--------------+---------+
|             9|   pepper|
+--------------+---------+
|            16|   tomato|
+--------------+---------+

recipe_ingredient
+----------+---------------+
| recipe_id|  ingredient_id|
+----------+---------------+
|         1|              7|
+----------+---------------+
|         1|             16|
+----------+---------------+
|         2|              7|
+----------+---------------+
|         3|              7|
+----------+---------------+
|         3|              9|
+----------+---------------+
|         3|             16|
+----------+---------------+

如何只显示那些成分完全相同的食谱？我用它

SELECT r.name, r.recipe_id
  FROM recipe AS r
    LEFT JOIN recipe_ingredient AS r_i ON r_i.ingredient_id = '7'
                 OR r_i.ingredient_id = '16'
WHERE r.recipe_id=r_i.recipe_id

但它不能按我的需要工作。最后，我想得到这个结果。

+----------+---------------+
|      name|      recipe_id|
+----------+---------------+
|    name_1|              1|
+----------+---------------+
|    name_3|              3|
+----------+---------------+

请帮忙

P.S:抱歉我的英语不好

Answer 1

SELECT r.name, r.recipe_id
FROM recipe AS r
JOIN (SELECT recipe_id,
             COUNT(*) c AS total_ingredients,
             SUM(ingredient_id IN (7, 16)) AS matching_ingredients
      FROM recipe_ingredient
      GROUP BY recipe_id
      HAVING total_ingredients = 2 AND matching_ingredients = 2) AS r_i
ON r.recipe_id = r_i.recipe_id

一般来说，HAVING子句中的值应该是IN子句中成分的数量。