Sql serverrRecursive query for BOM not showing child parts

Question

I have three tables:

1. SO_Items Sales orders - SOCTE lists all Parts that have live sales orders
2. Components - links a part with it's component part ( this can be several layers deep ). CR lists all parts and it's components using recursion.
3. WO Works orders - WOCTE lists all Parts that have active sales orders and their respective work order.

The problem I have is:

I have a sales order for Part 100831 which is made from 100831-U. I have a works order number 10 for 100831 and 11 for 100831-U.

The result of my query is:

Part     WO
100831   10
100831-U 11

which is correct.

But I would like it to be

Part   WO
100831 10
100831 11

I only want the Part with the sales order listed with all its and its components works orders. I have tried using variables but I can't seem to get it right.

Any help will be greatly appreciated

I have created a 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 I am trying to get just the Parts in SO_Items (sales orders) with all the WO_No (work order numbers) that are in it's BOM. (Bill of materials)

|      PART | WO |
|-----------|----|
|    100831 | 10 |
|    100831 | 11 |
|    106061 | 12 |
|     43567 | 15 |
|    106061 | 13 |
|    106061 | 14 |

WITH SOCTE (Part) as
(
SELECT DISTINCT SO_Items.Part_No
FROM  SO_Items
WHERE (SO_Items.Fully_Del='N') 
),

CR (Part,Component) as
(
SELECT  SOCTE.Part , Components.Component_Part_No 
FROM Components
JOIN SOCTE 
ON Components.Parent_Part_No = SOCTE.Part
UNION ALL
SELECT Parent_Part_No,Component_Part_No 
FROM Components
INNER JOIN CR AS SCR ON SCR.Component = Components.Parent_Part_No
),

WOCTE(Part,WO) as 
(
Select  Distinct Part ,WO.WO_No
FROM  WO 
Inner JOIN CR 
ON CR.Part=WO.Part_No and WO.WO_Complete=0 and   WO.ReProcess=0
)
SELECT Part,WO FROM WOCTE
Order by Part

Answer 1

OK, your updated explanation helped a lot.

I think I have it:

;WITH SOCTE (Part) as (
    SELECT DISTINCT SO_Items.Part_No
    FROM  SO_Items
    WHERE (SO_Items.Fully_Del = 'N') 
),
CR (Part, SO_Part, Component, Step) as (
    SELECT SOCTE.Part, SOCTE.Part AS "SO_Part", Components.Component_Part_No, 0
    FROM Components
    JOIN SOCTE 
        ON Components.Parent_Part_No = SOCTE.Part

    UNION ALL

    SELECT Components.Parent_Part_No, SCR.SO_Part, Components.Component_Part_No, SCR.Step + 1
    FROM Components
    INNER JOIN CR AS SCR 
        ON SCR.Component = Components.Parent_Part_No
),
WOCTE (Part, WO) as (
    SELECT DISTINCT CR.SO_Part, WO.WO_No
    FROM WO 
    INNER JOIN CR 
        ON  CR.Part        = WO.Part_No 
        AND WO.WO_Complete = 0
        AND WO.ReProcess   = 0
)
SELECT *
FROM WOCTE
ORDER BY Part

It's not perfect and I have extra fields in there. I decided that if you couldn't make one field do two things, then maybe there should be a second field. The Step field is just to see how the CTE is recursing. Using SELECT * FROM CR with the step numbers helped to see where the fields were going and what I needed to preserve.