SQL(ite) 过滤一对多表

Question

与 table

CREATE TABLE recipeingredients(recipeid int, ingredientid int);
CREATE TABLE ingredients(ingredientid int primary key, name text, perishable int);

配料

1    'ingA'    0
2    'ingB'    1
3    'ingC'    1
4    'ingD'    0

配方成分

1    1
1    2
2    1
2    2
2    3
3    2
3    4

recipeingredients 表用于存储配方可能包含的所有成分，而成分表用于存储每种独特成分的质量。

我通过查询将 recipeingredients 加入到成分中:

SELECT * FROM recipeingredients
INNER JOIN (SELECT ingredientid, name, perishable FROM ingredients) i
ON recipeingredients.ingredientid = i.ingredientid

因为“易腐烂”的数据。

现在，如果我想从选择中排除所有具有给定静态成分 ID 列表之一的食谱，我将如何去做呢？

我尝试了直观的选择

SELECT * FROM recipeingredients
INNER JOIN (SELECT ingredientid, name, perishable FROM ingredients
            WHERE ingredientid NOT IN (1, 3)
           ) i
ON recipeingredients.ingredientid = i.ingredientid

这显然是行不通的。

也就是说，这是我想要返回的选择，假设禁止的成分 ID 是 1 和 3:

recipeid|ingredientid| name |perishable  
3     2     'ingB' 1
3     4     'ingD' 0

因为食谱 1 和 2 都包含 1 或 3。

我将如何进行此选择？我需要一个新的成分表来过滤配方 ID 吗？

Answer 1

您不需要使用子查询来进行连接，如果两个列名匹配，则可以使用 USING 子句简化连接:

SELECT recipeid,
       ingredientid,
       name,
       perishable
FROM recipeingredients
JOIN ingredients USING (ingredientid);

无论如何， SELECT ... FROM ingredients 上的 WHERE 子句子查询返回不在您列表中的所有成分。

如果要排除所有具有给定静态成分 ID 列表之一的食谱，则必须用 SQL 编写。

此查询将选择具有给定静态成分 ID 列表之一的所有配方 ID:

SELECT DISTINCT recipeid
FROM recipeingredients
WHERE ingredientid IN (1, 3);

然后此查询从结果中排除这些食谱:

SELECT recipeid,
       ingredientid,
       name,
       perishable
FROM recipeingredients
JOIN ingredients USING (ingredientid)
WHERE recipeid NOT IN (SELECT recipeid
                       FROM recipeingredients
                       WHERE ingredientid IN (1, 3));