Hi Cetin,

>You're trying but I'm having a hard time to understand - in pseudocode and expected results, or a diagram what comes from where sample might understand but who has time to create that:)

True, I'm finding it harder to explain than to solve :=}

Here's the script to create some sample data to run the query against.

/****** Object:  Table [dbo].[RecipeList]    Script Date: 05/02/2007 14:32:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestR](
	[id] [int] NOT NULL,
	[recipeid] [int] NOT NULL,
	[quan] [numeric](7, 2) NOT NULL,
	[portion] [bit] NOT NULL,
	[itemid] [int] NOT NULL,
	[itemisrecipe] [bit] NOT NULL,
	[expandthis] [bit] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
VALUES     (70, 23, 1.75, 1, 230, 1, 1)
GO
INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
VALUES     (71, 23, 70, 0, 21, 0, 0)
GO
INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
VALUES     (72, 23, 15, 0, 31, 0, 0)
GO
INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
VALUES     (62, 230, 125, 0, 4, 0, 0)
GO
INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
VALUES     (63, 230, 5, 0, 11, 0, 0)
GO
INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
VALUES     (94, 230, 100, 0, 231, 1, 1)
GO
INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
VALUES     (64, 231, 80, 0, 23, 0, 0)
GO
INSERT INTO TestR(id, recipeid, quan, portion, itemid, itemisrecipe, expandthis)
VALUES     (65, 231, 5, 0, 11, 0, 0)

In English this means:
Recipe 23 contains 1.75 portions of Recipe 230, 70gms of ingredient 21 and 15gms of ingredient 31.
Recipe 230, in turn, contains 125gms of Ingredient 4, 5 gms of ingredient 11 and *100gms* of recipe 231 (portion=false).
Recipe 231 contains 80gms of ingredient 23 and 6gms of ingredient 11.

So the correct solution is:
70gms of ingredient 21
15gms of ingredient 31
218.75gms of ingredient 4
~19.044 gms of ingredient 11
~164.70 gms of ingredient 23

The query, as is, ignores the 'portion' field (i.e treats it as true for all rows) and, in that scenario, returns the correct result. But because of this the amount for the ingredient id 23 is calculated as 14000 (80*100*1.75) but it should be ~164.70 (80/85*100*1.75 85 being the total weight of recipe 231). Obviously the same error applies to ingredient 11 where used in that recipe.

Reading this I've a feeling I still failed to explain things clearly enough but......time to pack up for the day.

>Anyway if you've a working C# version then why not add it as an SP to SQL server:)

Under consideration......
Best,
Viv