Processing BOM Rule instance resolution with SQL Server

You can solve this problem by executing two counts. The first count simply calculates the raw material needed for each recipe; the second count joins the Recipeingredients table with the ingredients table before counting. Reduce the count by removing any needed ingredients from the pantry, and then by comparing the count, you can reject recipes that you can't get all the ingredients, and you can accept recipes that match the conditions. I have provided an example in listing a. (The database in Listing A is a little more complicated than what we're talking about, and it has some query tables and some columns that aren't mentioned.) ）

Since there are two counts (needed and available), then we have to make a simple comparison of them. I usually take the atom to the molecular query, so I keep each statement in view (Ingredientsavailable_by_recipe_vue and Ingredientsrequired_by_recipe_vue). I then created a third view with the Recipeid field as a join condition, adding a condition that the available (Available) must equal the required (Required). To make this list more appealing, I added the recipes table to it, so I could get the name of the recipe. You can view listing B. You can also remove the logic to view recipes, and you can change the condition of the where statement to required > Available, but this does not simplify the operation.

Listinga:

/* count the ingredients required */SELECT　　　
[Recipe Ingredients].RecipeID,　　　
Count([Recipe Ingredients].IngredientID) AS RequiredFROM　　　
[Recipe Ingredients]GROUP BY　　　
[Recipe Ingredients].RecipeID;
/* count the ingredients available */SELECT　　　
[Recipe Ingredients].RecipeID,　　　
Count(Pantry.IngredientID) AS AvailableFROM Pantry　　　
RIGHT JOIN [Recipe Ingredients]　　　
ON Pantry.IngredientID
= [Recipe Ingredients].IngredientIDGROUP BY　　　
[Recipe Ingredients].RecipeID;

LISTINGB:

SELECT　　　
IngredientsRequired_by_Recipe_qs.RecipeID,　　　
Recipes.RecipeNameFROM　　　
(IngredientsAvailable_By_Recipe_qs　　　
INNER JOIN IngredientsRequired_by_Recipe_qs　　　
ON IngredientsAvailable_By_Recipe_qs.RecipeID = IngredientsRequired_by_Recipe_qs.RecipeID)　　　
INNER JOIN Recipes　　　
ON IngredientsRequired_by_Recipe_qs.RecipeID
= Recipes.RecipeIDWHERE (([Available]>=[Required]));

In the early days, I assumed that any number appearing in the pantry would indicate that the material was adequate. A quantity column is required in the Recipeingredients table, and a quantity column is also required in the Pantry table. (This makes the example more complicated; For example, I bought salt, its unit pound or kilogram, and the recipe requires a spoonful of spoon or spoon?) ）

If you have quantity this column, a new challenge arises: if you want to invite friends for lunch, your menu uses guacamole, Mexican salad and beer. The task now is to compare the variety with the existing variety in the pantry and then create a list that needs to be purchased. I leave this question to interested readers to realize.

Note: You can also download a. NET executable program before you create a database and create objects in your database that are of interest to you, including the data in the table. You need to install it. NET and SQL Server 2005 to execute the code. This program is a winrar file, but the inside is. NET executable. The code can be created from the Red Gate software SQL Packager, but you don't need red gate to run it.