Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
iulianionescu
Starting Member
14 Posts |
Posted - 2007-07-19 : 22:57:23
|
I have a situation that can best be described simplified like this: there are 3 tables, one with generic food, one with custom food and one with receipes. Each receipe can be made up of one or more generic foods and/or one or more custom foods and/or one or more receipes. Let me give you an example:Generic foods:1, chicken2, ham3, cheeseCustom food:1, garlic bread2, purple cheeseReceipe:1, (chicken, garlic bread) - receipe 12, (cheese, purple cheese) - receipe 23, (receipe 1, ham)So, the third receipe contains receipe 1 and one generic food. All the foods have certain nutrient contents. I need to compute the nutrient contents of any food including receipes. I got stuck at how I can expand the nested receipes. For instance, in the case of receipe number 3, it's ingredients are:receipe 1,hamThis should expand to:chickengarlic breadhamThe way the ingredients are saved in the receipe is with a flag at the end that can be 0 (generic), 1 (custom), 2 (receipe).The problem is the the nesting should theoretically be infinite. In other words each receipe can contain other receipes in it. How can I break down the "tree" of receipes into basic foods? I can't use cursors because the application should return live results to logged users. I thought maybe I can use cursors to scroll through the receipe ingredient and each time I find a receipe nested I call the same procedure, but that of course doesn't work because the cursor is already declared and I also get an error that Insert cannot be nested with EXEC... There has to be a way that I can define a recurring procedure that retrieves records doing an insert into a memory table and then selecting results and everytime it encounters a nested receipe it calls itself.Anyone has any idea, or perhaps a different approach to the basic design itself?Thank you,IulianRegards,Iulian |
|
thewebhostingdir
Starting Member
3 Posts |
Posted - 2007-07-20 : 00:48:26
|
I will recommend you to change the structure and make it like this....TABLE "RECIPES"FIELD ID FOOD TYPE FOOD NAME INGREDIANTS1 GEN FOOD CHICKEN -NONE-2 GEN FOOD HAM -NONE- 3 GEN FOOD CHEESE -NONE-4 CUSTOM FOOD GARLIC BREAD -NONE-5 CUSTOM FOOD PURPLE CHEES -NONE-6 RECIPES RECIPES 1 1, 4 (e.g. chicken, garlic bread) 7 RECIPES RECIPES 2 3, 5 (e.g. CHEESE & PURPLE CHEES) 8 RECIPES RECIPES 3 RECIPES 1, 2 (e.g. Recipes, Ham)This way, you can easily use SP's and get your data in indefinite combination-------------------------Jason P.http://www.AccuWebHosting.ComSQL 2005/2008 Web Hosting |
|
|
iulianionescu
Starting Member
14 Posts |
Posted - 2007-07-22 : 13:48:49
|
Ok, I managed to solve this, but I was wondering if anyone can take a look and see any potential problem:The procedure must be called with a receipeID and amount = 1. Later the procedure will be recursed and the amount will contain the actual amount of each receipe type ingredient:alter PROCEDURE hlt_food_ReadCustomReceipeIngredients2 @ReceipeID int, @Amount float ASBEGIN SET NOCOUNT ON;-- declare a temp table to hold the ingredient type = 2 (receipe) declare @tmp table (NDB_No int, Amount float, Seq int, Long_Desc varchar(255), IngredType int)-- select all receipes in the temp table INSERT INTO @tmp (NDB_No, Amount, Seq, Long_Desc, IngredType) SELECT hlt_food_CUST_INGRED.NDB_No, hlt_food_CUST_INGRED.Amount, hlt_food_CUST_INGRED.Seq, hlt_food_CUST_RECEIPE.FoodName, 2 FROM hlt_food_CUST_INGRED INNER JOIN hlt_food_CUST_RECEIPE ON hlt_food_CUST_INGRED.NDB_No = hlt_food_CUST_RECEIPE.CustFoodID WHERE (hlt_food_CUST_INGRED.CustFoodID = @ReceipeID) AND (hlt_food_CUST_INGRED.IngredType = 2) declare @still bit set @still = 1 declare @id int declare @amt float WHILE (@still = 1) BEGIN-- get the ID and amount of the first receipe record set @id = (SELECT TOP 1 NDB_No from @tmp where (IngredType = 2)) if (@id is not null) BEGIN set @amt = (SELECT TOP 1 Amount from @tmp where (IngredType = 2))--if we found it, then insert in the tmp table the result by--calling the same procedure recursively INSERT INTO @tmp (NDB_No, Amount, Seq, Long_Desc, IngredType) EXEC hlt_food_ReadCustomReceipeIngredients2 @id, @amt-- delete that first record we just processed DELETE TOP(1) FROM @tmp where (IngredType = 2) END ELSE--if we are here it means we finished all records where IngredType = 2-- and they were replaced by basic records. set @still = 0 END--here we do the actual selection. We must multiply the --amount by @Amount to account for sub-ingredients being multiplied--by the amount of the parent receipe. SELECT hlt_food_CUST_INGRED.NDB_No, hlt_food_CUST_INGRED.Amount*@Amount As Amount, hlt_food_CUST_INGRED.Seq, hlt_food_FOOD_DES.Long_Desc, 0 FROM hlt_food_CUST_INGRED INNER JOIN hlt_food_FOOD_DES ON hlt_food_CUST_INGRED.NDB_No = hlt_food_FOOD_DES.NDB_No WHERE (hlt_food_CUST_INGRED.CustFoodID = @ReceipeID) AND (hlt_food_CUST_INGRED.IngredType = 0) UNION ALL SELECT hlt_food_CUST_INGRED.NDB_No, hlt_food_CUST_INGRED.Amount*@Amount As Amount, hlt_food_CUST_INGRED.Seq, hlt_food_CUST_FOOD.Long_Desc, 1 FROM hlt_food_CUST_INGRED INNER JOIN hlt_food_CUST_FOOD ON hlt_food_CUST_INGRED.NDB_No = hlt_food_CUST_FOOD.NDB_No WHERE (hlt_food_CUST_INGRED.CustFoodID = @ReceipeID) AND (hlt_food_CUST_INGRED.IngredType = 1) UNION ALL SELECT NDB_No, Amount*@Amount As Amount, Seq, Long_Desc, IngredType FROM @tmp ORDER BY Long_Desc END The question I have: are there any issues in using SELECT TOP 1, process it and then DELETE TOP(1). Any chance that SELECT TOP 1 and DELETE TOP(1) will not affect the same record if I have an Insert between them?Thanks,IulianRegards,Iulian |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-07-23 : 00:41:31
|
quote: Originally posted by thewebhostingdir I will recommend you to change the structure and make it like this....TABLE "RECIPES"FIELD ID FOOD TYPE FOOD NAME INGREDIANTS1 GEN FOOD CHICKEN -NONE-2 GEN FOOD HAM -NONE- 3 GEN FOOD CHEESE -NONE-4 CUSTOM FOOD GARLIC BREAD -NONE-5 CUSTOM FOOD PURPLE CHEES -NONE-6 RECIPES RECIPES 1 1, 4 (e.g. chicken, garlic bread) 7 RECIPES RECIPES 2 3, 5 (e.g. CHEESE & PURPLE CHEES) 8 RECIPES RECIPES 3 RECIPES 1, 2 (e.g. Recipes, Ham)This way, you can easily use SP's and get your data in indefinite combination-------------------------Jason P.http://www.AccuWebHosting.ComSQL 2005/2008 Web Hosting
Uh huh... that I'd like to see... especially the "easily" part.--Jeff Moden |
|
|
|
|
|
|
|