How can i fetch recursive data from Bill of materials tables

Question

Hi,

This is in SQL Server 2008.

I plan to create two tables to represent a Bill of material. The Item Master will have data as follows :-

[ID] [Item Code] [Item Description]

1---- A001 ---- Assembly 1
2---- B001 ---- Sub Assembly 1 (Child of Assembly 1)
3---- B002 ---- Sub Assembly 2 (Child of Assembly 1)
4---- C001---- Component 1 (Child of Sub Assembly 1)
5---- C002---- Component 2 (Child of Sub Assembly 2)

The BOM Relation Table will have data as follows . [Parent Item ID] and [Child Item ID] are Foreign keys to Item master. :-

[ID] [Parent Item ID] [Child Item ID]

1---- 1---- 2
2---- 1---- 3
3---- 2---- 4
4---- 3---- 5

So the first table has just the items themselves and the other table has relationships in terms of which parent ID has which children.

What could be the SQL to fetch all the child items for the Assembly (A001) given the fact that it may have to iterate recursively depending on data added to above tables ?

So for above data, i should get the output as follows :-

1) A001

                1.1) B001       
                               1.1.1)C001      
                1.2) B002         
                               1.2.1) C002  

Thanks, Chak.

Answer 1

You would need to use a recursive common table expression to do this with your current structure.

There is also a hierarchyid type in SQL Server 2008 that may make things simpler.

Answer 2

Use a recursive CTE. For example:

WITH BomTree (ID, Level, [Item Code], [Item Description], Depth)
AS
(
-- Anchor member definition
    SELECT m.*, 0 AS Depth
    FROM dbo.Master AS m
    WHERE m.[ID] = ?
    UNION ALL
-- Recursive member definition
    SELECT m.*, t.Depth + 1 AS Depth
    FROM dbo.Master AS m
    INNER JOIN dbo.BOM AS b
        ON m.[ID] = b.[Child Item ID]
    INNER JOIN BomTree AS t
        ON b.[Parent Item ID] = t.ID
)
-- Statement that executes the CTE
SELECT * FROM BomTree;