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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Breaking down nested records

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, chicken
2, ham
3, cheese

Custom food:
1, garlic bread
2, purple cheese

Receipe:
1, (chicken, garlic bread) - receipe 1
2, (cheese, purple cheese) - receipe 2
3, (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,
ham

This should expand to:

chicken
garlic bread
ham

The 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,

Iulian


Regards,

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 INGREDIANTS

1 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.Com
SQL 2005/2008 Web Hosting
Go to Top of Page

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 
AS
BEGIN
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,

Iulian

Regards,

Iulian
Go to Top of Page

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 INGREDIANTS

1 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.Com
SQL 2005/2008 Web Hosting



Uh huh... that I'd like to see... especially the "easily" part.

--Jeff Moden
Go to Top of Page
   

- Advertisement -