如何进行查询以显示? [英] How to make a query to display?

问题描述

我有 3 张桌子

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|
+----------+---------------+

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

how to display only those recipes in which the ingredients are strictly identical? I use it

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

但它没有按我需要的那样工作.最后，我想得到这个结果.

but it does not work as I need to.In the end, I want to get this result.

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

请帮忙

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

P.S: Sorry for my bad english

推荐答案

SELECT `r`.`recipe_id`, `r`.`name`
    FROM `recipe` `r` 
    JOIN ( SELECT `r_i`.`recipe_id`
           FROM `recipe_ingredient` `r_i`
           WHERE `r_i`.`ingredient_id` IN ( 7,16 )
           GROUP BY `r_i`.`recipe_id`
           HAVING COUNT(`recipe_id`) >= 2
    ) `result` ON `r`.`recipe_id` = `result`.`recipe_id`

示例:

$ingred_arr = array(7,16);
$count_ingred = count($ingred_arr);

$query = "
    SELECT `r`.`recipe_id`, `r`.`name`
    FROM `recipe` `r` 
    JOIN ( SELECT `r_i`.`recipe_id`
           FROM `recipe_ingredient` `r_i`
           WHERE `r_i`.`ingredient_id` IN ( ".implode(',',$ingred_arr)." )
           GROUP BY `r_i`.`recipe_id`
           HAVING COUNT(`recipe_id`) >= ".$count_ingred."
    ) `result` ON `r`.`recipe_id` = `result`.`recipe_id` "

结果:

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

这篇关于如何进行查询以显示?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！