MySQL选择具有多个n到n

本文介绍了MySQL选择具有多个n到n的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一些与此类似的表

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.