# How to construct a set-based query for subset problem?

I have a DB of the following structure (picture shows simplified version)

How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe requiredQuaintity > availableQuantity?

I tried this:

``````SELECT r.Name
FROM
Receipe r
JOIN RecipeIngredients ri ON ri.RecipeID = r.Id
JOIN Ingredients i ON i.ID = ri.IngredientsId
WHERE
ri.RequiredQuantity - i.AvailableQuantity > 0
``````

but am not sure this is correct as I think this will only return available ingredients. How should I modify above query to produce only receipees where each of ingredient is available? Thank you for help

---- Edit ----

Maybe something like this:

``````SELECT r.Name
FROM
(
SELECT   r.Name AS Name
r.Id AS Id
, CASE (
WHEN (ri.RequiredQuantity - i.AvailableQuantity >= 0)
THEN 1
) AS Available
FROM
Receipe r
JOIN RecipeIngredients ri ON ri.RecipeID = r.Id
JOIN Ingredients i ON i.ID = ri.IngredientsId
WHERE
ri.RequiredQuantity - i.AvailableQuantity >= 0
GROUP BY
r.Id
) AS results
WHERE
// count of ingredients with value 1 for each recipe == count of all required ingredients for this recipe
``````

Assuming referential integrity and all columns to be `NOT NULL`, this should be simplest and fastest:

``````SELECT *
FROM   Receipe r
WHERE  NOT EXISTS (
SELECT 1
FROM   Ingredients i
JOIN   RecipeIngredients ON ri.IngredientsId = i.ID
WHERE  ri.RecipeID = r.Id
AND    ri.RequiredQuantity > i.AvailableQuantity
);
``````

Basically, use a `NOT EXISTS` anti-semi-join to rule out recipes with any shortcomings. As soon as the first is found, Postgres can drop the recipe at hand from the result and move on.

Aside: My standing advice is not to use CaMeL-case identifiers in Postgres.

• +1, except for the stylistic aside. What you're implementing here is relational division, which is notoriously awkward in SQL. Think of it this way: because `recipes * recipe ingredients = ingredients`, then `ingredients / recipe ingredients = recipes` Jul 28, 2014 at 1:59

Here is one way to do:

``````select id,name from recipe;

1   Recipe 1
2   Recipe 2
3   Recipe 3

select recipe_id,ingredient_id,required_quantity from recipe_ingredients;

1   1   10
1   2   5
1   4   10
2   1   10
2   2   5
2   3   20

select id,name,available_quantity from ingredients;

1   Ingredient 1    100
2   Ingredient 2    10
3   Ingredient 3    30
4   Ingredient 4    5

with get_recipe
AS (
select r.name,count(1) require,sum(case when i.available_quantity - ri.required_quantity >= 0 then 1 else 0 end) available
from recipe r, recipe_ingredients ri, ingredients i
where r.id = ri.recipe_id
and ri.ingredient_id = i.id
group by r.name
)
select name, require,available from get_recipe
where require = available;

Recipe 2    3   3
``````