Find The Names Of Recipes Where At Least 3 Oz. (quantity) Of Every Ingredient Are Used

INGREDIENT(ingredient-id,name,price-ounce) RECIPE(recipe-id,name,country,time) USES(rid,iid,quantity) rid is a foreign key to recipe-id and iid is a foreign key ingredient_id Find

Solution 1:

The subquery will match any "Uses" row that has quantity >= 3. So, you will end with the list of recipes that has at least one ingredient with quantity >= 3.

What you want is that none of Uses should be less than 3 in order to match a recipe.

I would do some "double negative" here (NOT IN, and use "<" instead of ">=") :

SELECT  name
FROM    recipe
WHERE   recipe_ID NOTIN(SELECT rid
            FROM    USES
            WHERE   quantity<3);

With this you will get every recipe that doesn't have any ingredients with quantity less than 3, which is equivalent to having all ingredients with quantity greater than 3.