sql server - Sql serverrRecursive query for BOM not showing child parts -

i have 3 tables:

1. so_items sales orders - socte lists parts have live sales orders
2. components - links part it's component part ( can several layers deep ). cr lists parts , it's components using recursion.
3. wo works orders - wocte lists parts have active sales orders , respective work order.

the problem have is:

i have sales order part 100831 made 100831-u. have works order number 10 100831 , 11 100831-u.

the result of query is:

part     wo 100831   10 100831-u 11 

which correct.

but be

part   wo 100831 10 100831 11 

i want part sales order listed , components works orders. have tried using variables can't seem right.

any appreciated

i have created sql fiddle http://sqlfiddle.com/#!3/c77a5/1

the current output is:

|      part | wo | |-----------|----| |    100831 | 10 | |  100831-u | 11 | |    106061 | 12 | |     43567 | 15 | | r106061-1 | 13 | | r106061-1 | 14 | 

and trying parts in so_items (sales orders) wo_no (work order numbers) in it's bom. (bill of materials)

|      part | wo | |-----------|----| |    100831 | 10 | |    100831 | 11 | |    106061 | 12 | |     43567 | 15 | |    106061 | 13 | |    106061 | 14 |  socte (part) ( select distinct so_items.part_no  so_items (so_items.fully_del='n')  ),  cr (part,component) ( select  socte.part , components.component_part_no  components join socte  on components.parent_part_no = socte.part union select parent_part_no,component_part_no  components inner join cr scr on scr.component = components.parent_part_no ),  wocte(part,wo)  ( select  distinct part ,wo.wo_no  wo  inner join cr  on cr.part=wo.part_no , wo.wo_complete=0 ,   wo.reprocess=0 ) select part,wo wocte order part 

ok, updated explanation helped lot.

i think have it:

;with socte (part) (     select distinct so_items.part_no      so_items     (so_items.fully_del = 'n')  ), cr (part, so_part, component, step) (     select socte.part, socte.part "so_part", components.component_part_no, 0     components     join socte          on components.parent_part_no = socte.part      union      select components.parent_part_no, scr.so_part, components.component_part_no, scr.step + 1     components     inner join cr scr          on scr.component = components.parent_part_no ), wocte (part, wo) (     select distinct cr.so_part, wo.wo_no     wo      inner join cr          on  cr.part        = wo.part_no          , wo.wo_complete = 0         , wo.reprocess   = 0 ) select * wocte order part 

it's not perfect , have fields in there. decided if couldn't make 1 field 2 things, maybe there should second field. step field see how cte recursing. using select * cr step numbers helped see fields going , needed preserve.