I recently came up with a problem which I would like to share some thoughts about with someone on this forum. This relates to finding a subset. In reality it is more complicated, but I tried to present it here using some simpler concepts.
To make things easier, I created this conceptual DB model:
Let’s assume this is a DB for storing recipes. Recipe can have many instructions steps and many ingredients.
Ingredients are stored in a cupboard and we know how much of each ingredient we have.
Now, when we create a recipe, we have to define how much of each ingredient we need. When we want to use a recipe, we would just check if required amount is less than available amount for each product and then decide if we can cook a dinner – if amount required for at least one ingredient is less than available amount – recipe cannot be cooked. Simple sql query to get the result.
This is straightforward, but I’m wondering, how should I work when the problem is stated the other way round, i.e. how to find recipies which can be cooked only from ingredients that are available?
I hope my explanation is clear, but if you need any more clarification, please ask.
6
After your clarifications on the question, I would go by this logic to list recipeids excluding any recipe with a missing/insufficient ingredient.
SELECT recipeid
FROM recipeingredients RI
WHERE recipeid NOT IN (SELECT RI1.recipeid
FROM recipeingredients RI1
LEFT OUTER JOIN Ingredients I ON RI1.ingredientsid = I.id
AND RI1.Requiredquantity <= I.availablequantity
WHERE I.id IS NULL
)
Reasonably simple, using a subquery join.
- JOIN Recipeingredients on Ingredients where RequiredQuantity less or equal AvailableQuantity. Test this to make sure it works as expected.
- JOIN Recipe IN above subquery to find those you can make.
I’ll leave you to write the actual SQL.