simplest way to do recursive t-sql for multiple selects

Question

Hi all,

I am developing a Bill Of Materials cost calculator program and I am struggling to fathom a simple solution to some recursive selects I want.

I am using SQL Server 2005 for this part of the application.

Say I have Product A, which contains assembly B, and Part C. Assembly B will contain parts D and E, but, here is where I struggle, D and or E may contain X number of other assemblies.

I can do something along the lines of;

SELECT * FROM TBLBOM WHERE Parent = A 
UNION 
SELECT * FROM TBLBOM WHERE Parent = B 
UNION 
SELECT * FROM TBLBOM WHERE Parent = C

To produce something along the lines of;

PARENT    COMP    COST
A          X       £1
B          D       £0.5
B          E       £0.5
....
C          Y       £1

But lets say Component D is made up of Component F & G, how would I accommodate this in a t-sql statement.

In a nutshell, I need to expand out the full component list of all assemblies that are associated to a parent product regardless of whether they are in a sub assembly or a sub assembly of a sub assembly etc...

Ideally I would like to avoid a cursor at all costs :)

Any help / guidance would be appreciated.

Thank you.

EDIT; As requested, here is the table structure and expected output. The parent is the DRAWINGNO and the child node is the PART (which could also be a parent in itself);

BOMID      DRAWINGNO         ITEM            PART               COST     
1303       HGR05180           1              HGR05370           1
1304       HGR05180           2              HGF65050           4
1305       HGR05180           3              HGF50340           1 
1312       HGR05370           1              HPN05075           1 
1313       HGR05370           2              HPN05085           2
1314       HGR05370           3              HPN05080           1
1848       EXP-18G            1              HGR05180           1
1849       EXP-18G            2              HGR05210           3
1850       EXP-18G            3              HGR05230           1
1851       EXP-18G            4              HGR05140           1
1852       EXP-18G            5              HGR05150           2
1853       EXP-18G            6              HGR05050           1
1854       EXP-18G            7              ESC05350           1 
1855       EXP-18G            8              ESC05330           3 
1856       EXP-18G            9              HGR05360           1
1857       EXP-18G            10             HGR05370           2
1858       EXP-18G            11             ESC05640           1

Answer 1

You want to be using recursive common table expression (CTEs). Books Online has a lot of information on how to use these; in the index, look up CTEs and pick the "Recursive Queries Using Common Table Expressions" entry. (I've had problems before linking to BOL online, or I'd try to link it here.)

Also, if you post your table structure, you should get half a dozen examples within five minutes. Better yet, try and search SO for prior examples.

Answer 2

If i understand (and without table structure) you can try something like this

DECLARE @Table TABLE(
     Component VARCHAR(50),
     Parent VARCHAR(50),
     Cost FLOAT
)

INSERT INTO @Table SELECT 'B', 'A', 1
INSERT INTO @Table SELECT 'C', 'B', 2
INSERT INTO @Table SELECT 'C', 'B', 3
INSERT INTO @Table SELECT 'D', 'C', 4

DECLARE @Product VARCHAR(50)
SET @Product = 'A'

;WITH Selects AS (
     SELECT *
     FROM @Table
     WHERE Parent = @Product
     UNION ALL
     SELECT t.*
     FROM @Table t INNER JOIN
       Selects s ON t.Parent = s.Component
)

SELECt  *
FROm    Selects