MySQL选择具有多个n到n [英] mysql select having multiple n to n's

问题描述

我有一些与此类似的表

recipes_tbl
| id | Recipe | Directions |

ingredients_tbl
| id | Ingrediant | Category |

recipe_to_ingredient
| id | id_recipe  | id_ingredient | Quantity

我正在尝试建立一个查询，以获取所有使用牛肉(id_ingredient 1)和土豆(id_ingredient 2)和... n的食谱"

I'm trying to build a query to "Get all recipes that use beef(id_ingredient 1) and potatoes(id_ingredient 2) and ...n"

我怀疑solutios涉及某种花哨的连接...也许吗?

I suspect the solutios involves some kind of fancy joins... maybe?

推荐答案

SELECT     R.ID, R.Recipe, R.Directions
FROM       Ingredients_tbl I
           INNER JOIN recipe_to_ingredient RI ON I.id = RI.id_ingredient 
           INNER JOIN recipes_tbl R ON R.id = R.id_recipe  
WHERE      I.ID IN (1 ,2)
GROUP BY   R.ID, R.Recipe, R.Directions
HAVING     COUNT(*) > 1

应该这样做，尽管本示例中的成分被硬编码为1或2.我需要更多地了解您打算如何提供成分ID，以便在此方面提供更多内容.

Should do it, though the ingredients in this example are hardcoded to only ever be 1 or 2. I'd need to know more about how you intend on supplying the ingredient id's to provide more on that front.

这篇关于MySQL选择具有多个n到n的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！