如何仅在满足多个条件时返回一行

Question

我正在开发一个液体分配应用程序。以下是数据库的组织结构：

CANISTER:

• canister_id{PK}
• ingredient_id{FK}

INGREDIENT:

• ingredient_id{PK}
• ingredient_name

DRINK:

• drink_id{PK}
• drink_name

INGREDIENTINSTANCE:

• instance_id{PK}
• drink_id{FK}
• ingredient_id{FK}
• amount

每种饮料都有多种成分（保存在ingredientInstance表中），但只有12个罐子。我正在尝试编写一条SQL命令，以收集所有当前在罐子中具有其所需所有成分的饮料。

到目前为止，这就是我所拥有的。

SELECT DISTINCT Drink.drink_name
FROM Drink, ingredientInstance, Canister
WHERE (((ingredientInstance.drink_id)=[Drink].[drink_id])
AND ((ingredientInstance.ingredient_id) 
IN (select ingredient_id FROM Canister)));

然而，这会返回所有即使只有一个可用成分的饮料。我想要的是一种方法，确保对于ingredientInstance中的每个关联成分，它都当前在罐子里。

例如，假设drink1需要ingredient1和ingredient2。如果这两个成分ID都存在于Canisters中，则希望其出现在结果中，但如果只有一个或零个成分在罐子中，则不希望其出现在结果中。

我确定这是显而易见的事情，但我想不出如何做到这一点。

- Tadgh

---

你需要的是关系除法。看一下这个问题：如何在has-many-through关系中过滤SQL结果。 - ypercubeᵀᴹ

Answer 1

这是一个集合嵌套查询的示例。我建议在此使用带有having子句的聚合:

select drink.drinkname
from IngredientInstance ii join
     Drink d
     on ii.DrinkId = d.Drinkid left join
     Canister c
     on ii.IngredientId = c.INgredientId
group by drink.drinkname
having sum(iif(c.IngredientId is null, 1, 0)) = 0;

这里是将 IngredientInstance 和 Drink 表连接，仅获取饮料名称。然后进行 left join 到 Canister 表。这样可以保留所有饮料中的成分以及与罐子中匹配的成分（如果有的话）。如果缺少来自 Canister 的成分，则其具有 NULL 的 ingredientId。

group by 按照饮料的成分进行分组。最终的 having 子句计算缺少的成分数量，并仅返回没有缺失成分的饮料。

- Gordon Linoff

Answer 2

select d.* from drink d inner join
(select i.drink_id, sum(case when c.canister_id is null 1 else 0 end) as fullcanister
from ingredientinstance i left join canister c on c.ingredient_id=i.ingredient_id 
group by i.drink_id) as id on id.drink_id=d.drink_id and id.fullcanister=0

- vinodpthmn

---

这个不起作用。where子句所做的只是将left outer join转换为inner join。换句话说，它将返回在罐表中具有任何成分的饮料，而不是所有成分。 - Gordon Linoff