0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for enthusiasts and scholars of mythology and folklore

CREATE TABLE #t(
    pid varchar(20),
    cid varchar(20),
    qty float
)

INSERT #t(pid, cid, qty) VALUES 
        ('301755',   '31161201',    1     ),
        ('301755',   '29975413',    2     ),
        ('301755',   '299756',      2     ),
        ('301755',   '305958',      1     ),
        ('305958',   '311620',      4     ),
        ('305958',   '311620',       .1   ),
        ('299756',   'RDBSSL012',    .1   ),
        ('299756',   'RDBSSL012',   3.417 ),
        ('29975413', 'PLTSSL902',   1     ),
        ('29975413', 'CAPSSL4SCH40',1     ),
        ('29975413', 'PIPSSL4SCH40',3.96  ),
        ('29975413', 'LABSTR',       .166 ),
        ('31161201', 'PIPSSL2SCH40',4)

;WITH bom as (
  SELECT pid p,cid c, qty q, 0 bomlvl FROM #t WHERE pid='301755' -- start id
  UNION ALL
  SELECT pid, cid, q*qty, bomlvl+1 FROM #t INNER JOIN bom ON c=pid
)
SELECT * from bom a WHERE NOT EXISTS (SELECT 1 FROM bom WHERE p=a.c)

fork query

edited nov 19 anonymous

created aug 26 16 cars10