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
Best Answer
Assuming referential integrity and all columns to be
NOT NULL
, this should be simplest and fastest: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.