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 &gt; 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 &gt;= 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 &gt;= 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 &gt; 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.