SQL“ContainsAll”查询(SQL “ContainsAll” query)

SQL“ContainsAll”查询(SQL “ContainsAll” query)

我有食谱表和成分表和表将食材连接到配方。

我有一个成分列表，如何编写一个SELECT语句（或存储过程），它将返回具有所有给定成分的食谱？

如何为MySQL编写此查询？

I have recipes table and ingredients table and table that connect ingredients to the recipes.

I have a list of ingredients, how to write a SELECT statement (or store procedure) that will return a recipes that have ALL given ingredients?

How to write this query for MySQL?

最满意答案

我想我终于有了解决方案。 :)

SELECT * FROM recipeTable r JOIN ingredintsTable i ON r.RecId= i.RecId WHERE i.IngredientId IN (1,2) GROUP BY r.id HAVING ( COUNT(r.id) > 1 )

I think I finally have a solution. :)

SELECT * FROM recipeTable r JOIN ingredintsTable i ON r.RecId= i.RecId WHERE i.IngredientId IN (1,2) GROUP BY r.id HAVING ( COUNT(r.id) > 1 )