Database – An algorithm for finding subset matching criteria

algorithmsdatabasesql

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:

Recipe 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.

Best Answer

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
                      )
Related Topic