Hi Sergey,

>If you post comprehensive test data (CREATE TABLE, INSERTs), I can take a look at it.

Thx. Below is a simple set of data I'm working with (using different table/column names than my original post I'm afraid - recipe is now the equivalent of component) together with a query that seems to basically give the correct results.

However this data has a wrinkle - the 'portion' column indicates whether the 'quan' refers to a multiple of the recipe or to a fixed weight. For example assume recipe 231 is for Scotch & Water with 80gms of Scotch and 50gms of water. Id 94 (where portion=False) calls for 100g of Scotch & Water i.e. approx 61gms of id 64 (Scotch) and 38gms of id 65 (Water) (rather than 100 * 130gms) I'm stuck on this - especially since the two modes are mixed.

Anyway:

/****** 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)

Query:

WITH Parts AS (
-- Anchor
SELECT 
id,
recipeid,
quan,
itemid,
itemisrecipe
FROM TestR WHERE (recipeid = 23)

UNION ALL
-- Recursive
SELECT
r.id,
r.recipeid,
CAST(r.quan * p.quan AS numeric(7, 2)) AS quan,
r.itemid,
r.itemisrecipe
FROM TestR r INNER JOIN Parts p ON p.itemid = r.recipeid
WHERE (p.itemisrecipe = 1)
)

SELECT SUM(quan) quan, itemid,itemisrecipe
FROM Parts AS p2 WHERE itemisrecipe=0 GROUP BY itemid,itemisrecipe

Any suggestions gratefully accepted,
Viv