通过中间表进行SQL查询 [英] SQL query through an intermediate table

问题描述

给出以下表格:

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

我如何构造一个SQL查询来查找食材(named.name ='chocolate'和Ingredients.name ='cream')的食谱?

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

推荐答案

这称为关系划分. 尚未给出的另一种选择是双重不存在"

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

这篇关于通过中间表进行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！