1

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

2 Answers 2

2

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
  • +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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.