How can I get recursive data from material table tables

Question

This is in SQL Server 2008.

I plan to create two tables to represent Bill material. The item master will have the following data: -

[ID] [Product Code] [Product Description]

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

The specification relationship table will have the following data. [Parent element identifier] and [Parent element identifier] are foreign keys to the main object .: -

[ID] [Parent Identifier] [Child ID]

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

So, the first table has only the elements themselves, and the other table has relationships with which the parent identifier has children.

What could be the SQL to retrieve all the children of an assembly (A001), given the fact that iteration may be required recursively depending on the data added to the tables above?

So, for the data above, I should get the result as follows: -

1) A001

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

Thanks Chuck.

Answer 1

Use a recursive CTE . For instance:

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; 

Answer 2

, .

SQL Server 2008 hierarchyid, .