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

Question

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

enter image description here

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

Answer 1

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.

• James K. Lowden: +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

Answer 2

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