通过中间表查询SQL

Given the following tables:

鉴于以下表:

Recipes
| id | name
| 1  | 'chocolate cream pie'
| 2  | 'banana cream pie'
| 3  | 'chocolate banana surprise'

Ingredients
| id | name
| 1  | 'banana'
| 2  | 'cream'
| 3  | 'chocolate'

RecipeIngredients
| recipe_id | ingredient_id
|     1     |      2
|     1     |      3
|     2     |      1
|     2     |      2
|     3     |      1
|     3     |      3

How do I construct a SQL query to find recipes where ingredients.name = 'chocolate' and ingredients.name = 'cream'?

我如何构造一个SQL查询来查找配料。name = 'chocolate'和配料。name = 'cream'?

6 个解决方案

#1

This is called relational division. A variety of techniques are discussed here.

这叫做关系除法。这里讨论了各种技术。

One alternative not yet given is the double NOT EXISTS

一个尚未给出的选择是“不存在”

SELECT r.id, r.name
FROM Recipes r
WHERE NOT EXISTS (SELECT * FROM Ingredients i
                  WHERE name IN ('chocolate', 'cream')
                  AND NOT EXISTS
                      (SELECT * FROM RecipeIngredients ri
                       WHERE ri.recipe_id = r.id
                       AND ri.ingredient_id = i.id))

#2

Use:

使用:

  SELECT r.name
    FROM RECIPES r
    JOIN RECIPEINGREDIENTS ri ON ri.recipe_id = r.id
    JOIN INGREDIENTS i ON i.id = ri.ingredient_id
                      AND i.name IN ('chocolate', 'cream')
GROUP BY r.name
  HAVING COUNT(DISTINCT i.name) = 2

The key point here is that the count must equal the number of ingredient names. If it's not a distinct count, there's a risk of false positives due to duplicates.

这里的关键点是，计数必须等于成分名称的数量。如果这不是一个明显的计数，那么由于重复，就有可能出现假阳性。

#3

If you're searching for multiple associations then the simplest way to write the query is to use multiple EXISTS conditions instead of a single straight JOIN.

如果您正在搜索多个关联，那么编写查询的最简单方法是使用多个现有条件，而不是使用单个直接连接。

SELECT r.id, r.name
FROM Recipes r
WHERE EXISTS
(
    SELECT 1
    FROM RecipeIngredients ri
    INNER JOIN Ingredients i
        ON i.id = ri.ingredient_id
    WHERE ri.recipe_id = r.id
    AND i.name = 'chocolate'
)
AND EXISTS
(
    SELECT 1
    FROM RecipeIngredients ri
    INNER JOIN Ingredients i
        ON i.id = ri.ingredient_id
    WHERE ri.recipe_id = r.id
    AND i.name = 'cream'
)

If you know for sure that the associations are unique (i.e. a single recipe can only have a single instance of each ingredient), then you can cheat a bit using a grouping subquery with a COUNT function and possibly speed it up (performance will depend on the DBMS):

如果您确定关联是惟一的(即单个菜谱只能有单个的每个元素的实例)，那么您就可以使用一个COUNT函数的分组子查询进行欺骗，并可能加快它的速度(性能将取决于DBMS):

SELECT r.id, r.Name
FROM Recipes r
INNER JOIN RecipeIngredients ri
    ON ri.recipe_id = r.id
INNER JOIN Ingredients i
    ON i.id = ri.ingredient_id
WHERE i.name IN ('chocolate', 'cream')
GROUP BY r.id, r.Name
HAVING COUNT(*) = 2

Or, if a recipe might have multiple instances of the same ingredient (no UNIQUE constraint on the RecipeIngredients association table), you can replace the last line with:

或者，如果一个配方可能有多个相同成分的实例(在recipe配料关联表上没有唯一的约束)，您可以将最后一行替换为:

HAVING COUNT(DISTINCT i.name) = 2

#4

select r.*
from Recipes r
inner join (
    select ri.recipe_id
    from RecipeIngredients ri 
    inner join Ingredients i on ri.ingredient_id = i.id
    where i.name in ('chocolate', 'cream')
    group by ri.recipe_id
    having count(distinct ri.ingredient_id) = 2
) rm on r.id = rm.recipe_id

#5

SELECT DISTINCT r.id, r.name
FROM Recipes r
INNER JOIN RecipeIngredients ri ON
    ri.recipe_id = r.id
INNER JOIN Ingredients i ON
    i.id = ri.ingredient_id
WHERE
    i.name IN ( 'cream', 'chocolate' )

Edited following comment, thanks! This is the right way then:

编辑评论,谢谢!这是正确的方法:

SELECT DISTINCT r.id, r.name
FROM Recipes r
INNER JOIN RecipeIngredients ri ON
    ri.recipe_id = r.id
INNER JOIN Ingredients i ON
    i.id = ri.ingredient_id AND
    i.name = 'cream'
INNER JOIN Ingredients i2 ON
    i2.id = ri.ingredient_id AND
    i2.name = 'chocolate'

#6

a different way:

一种不同的方式:

Version 2 (as stored procedure) revised

版本2(作为存储过程)进行了修改

select   r.name
from   recipes r
where   r.id  = (select  t1.recipe_id
        from  RecipeIngredients t1 inner join
     RecipeIngredients     t2 on t1.recipe_id = t2.recipe_id
     and     t1.ingredient_id = @recipeId1
     and     t2.ingredient_id = @recipeId2)

Edit 2: [before people start screaming] :)

编辑2:(在人们开始尖叫之前):

This can be placed at the top of version 2, which will allow to query by name instead of passing in the id.

这可以放在版本2的顶部，它允许按名称查询，而不是传递id。

select @recipeId1 = recipe_id from Ingredients where name = @Ingredient1
select @recipeId2 = recipe_id from Ingredients where name = @Ingredient2

I've tested version 2, and it works. Most users where linking on the Ingredient table, in this case was totally not needed!

我已经测试了版本2，它是有效的。大多数用户在哪里链接配料表，在这种情况下完全不需要!

Edit 3: (test results);

3:编辑(测试结果);

When this stored procedure is run these are the results.

当运行这个存储过程时，这些就是结果。

The results are of the format (First Recipe_id ; Second Recipe_id, Result)

结果是格式(第一个Recipe_id;第二个Recipe_id,结果)

1,1, Failed
1,2, 'banana cream pie'
1,3, 'chocolate banana surprise'
2,1, 'banana cream pie'
2,2, Failed
2,3, 'chocolate cream pie'
3,1, 'chocolate banana surprise'
3,2, 'chocolate cream pie'
3,3, Failed

Clearly this query does not handle case when both constraints are the same, but works for all other cases.

显然，当两个约束都相同时，该查询不处理大小写，但适用于所有其他情况。

Edit 4:(handling same constraint case):

编辑4:(处理相同的约束情况):

replacing this line:

更换这条线:

r.id = (select t1...

来

r.id in (select t1...

works with the failed cases to give:

处理失败案例:

1,1, 'banana cream pie' and 'chocolate banana surprise'
2,2, 'chocolate cream pie' and 'banana cream pie'
3,3, 'chocolate cream pie' and 'chocolate banana surprise'

#1