DomingoR DomingoR - 7 months ago 6
SQL Question

Search parent-child relationship until parent meets a condition

My table looks like this:

ChildPart ParentPart Quantity ChildType
--------------------------------------------------
a0001 b0001 1 Bought
a0002 b0002 1 Bought
a0003 b0003 1 Bought
a0004 b0004 1 Bought
a0005 x0000 1 Made
b0001 c0001 1 Phantom
b0002 c0002 1 Phantom
b0003 x0000 1 External
b0004 c0004 1 Phantom
c0001 d0001 1 Phantom
c0002 x0000 1 External
c0004 d0004 1 Phantom
d0001 x0000 1 Made
e0004 x0000 1 External
x0000 x0000 1 Made


This table encloses a child-parent relationship of 4 elements. To give some extra detail, the ChildType specifies whether the part is bought, made or made by an external entity. I am interested in getting only the bought parts that are related with a parent that are done externally. However, the Phantom status should be ignored because it is a fake part made only to trace a minimal part transformation.

A better illustration of the process of every part is the following:

part a0001 -> b0001 -> c0001 -> d0001 -> x0000
type Bought - Phanto - Phanto - Made - Final Assembly (Made)

part a0002 -> b0002 -> x0002 -> d0000
type Bought - Phanto - Extern - Final Assembly (Made)

part a0003 -> b0001 -> x0000
type Bought - Extern - Final Assembly (Made)

part a0004 -> b0004 -> c0004 -> d0004 -> e0004 -> x0000
type Bought - Phanto - Phanto - Phanto - Extern - Final Assembly (Made)

part a0005 -> x0000
type Bought - Final Assembly (Made)


The final output I am interested to have is a table that relates the bought parts (a set of parts provided at the beginning) and their parents as long as they are external, and bypassing any Phantom in the middle.

If the part reaches a parent that is Made (anything else that is not External or Phantom), then it should return NULL or a flag indicating that this child does not have a Parent made externally.

I mean something like this:

ChildPart ExternalParent
-----------------------------
a0001 NULL
a0002 d0004
a0003 c0004
a0004 b0004
a0005 NULL


I been trying to use CTEs for this but without any luck yet...

This is my code. I intend to pair every child with their top External processed parent and then select the MainChild and ExternalParent columns.

DECLARE @BOM TABLE(
ChildPart VARCHAR(20)
ParentPart VARCHAR(20)
Quantity DEC(9,2)
ChildType VARCHAR(20)
)
INSERT INTO @BOM VALUES
('a0001','b0001',1,'Bought')
,('a0002','b0002',1,'Bought')
,('a0003','b0003',1,'Bought')
,('a0004','b0004',1,'Bought')
,('a0005','b0005',1,'Made')
,('b0001','c0001',1,'Phantom')
,('b0002','c0002',1,'Phantom')
,('b0003','c0003',1,'External')
,('b0004','c0004',1,'Phantom')
,('c0001','d0001',1,'Phantom')
,('c0002','d0002',1,'External')
,('c0004','d0004',1,'Phantom')
,('d0001','e0001',1,'Made')
,('e0004','f0004',1,'External')
;
DECLARE @partsToLook TABLE (ChildPart VARCHAR (20)
INSERT INTO @partsToLook VALUES ('a0001'),('a0002'),('a0003'),('a0004'),('a0005')

----
;WITH cte AS
(
SELECT
MainPart = p.ChildPart --This is to track the Main Child part we are looking the parents.
,ChildPart
,ParentPart
,Quantity
,ChildType
FROM @BOM b
INNER JOIN @partsToLook p ON p.ChildPart=b.ChildPart

UNION ALL

SELECT
MainPart = tb.ChildPart
,ChildPart
,ParentPart
,Quantity
,ChildType
FROM cte tb
INNER JOIN @BOM b ON b.ChildPart=tb.ParentPart
)
SELECT MainPart,ParentPart FROM cte

Answer

Your expected result in the question doesn't quite match the sample data. You'd better fix it to avoid confusion.

I added an explicit StopRecursion flag in the recursive query, which is set when recursion reaches a row that is not a 'Bought', 'Phantom', 'External'.

Then ROW_NUMBER is used to pick only one row per StartPart and StopRecursion flag is used to determine whether ExternalParent should be set to NULL.

Sample data

DECLARE @BOM TABLE(
    ChildPart VARCHAR(20)
    ,ParentPart VARCHAR(20)
    ,Quantity DEC(9,2)
    ,ChildType VARCHAR(20)
);

INSERT INTO @BOM (ChildPart,ParentPart,Quantity,ChildType) VALUES
 ('a0001','b0001',1,'Bought')
,('a0002','b0002',1,'Bought')
,('a0003','b0003',1,'Bought')
,('a0004','b0004',1,'Bought')
,('a0005','b0005',1,'Made')
,('b0001','c0001',1,'Phantom')
,('b0002','c0002',1,'Phantom')
,('b0003','c0003',1,'External')
,('b0004','c0004',1,'Phantom')
,('c0001','d0001',1,'Phantom')
,('c0002','d0002',1,'External')
,('c0004','d0004',1,'Phantom')
,('d0001','e0001',1,'Made')
,('e0004','f0004',1,'External')
;

DECLARE @partsToLook TABLE (ChildPart VARCHAR (20));
INSERT INTO @partsToLook (ChildPart) VALUES
('a0001'),
('a0002'),
('a0003'),
('a0004'),
('a0005');

Query

WITH
CTE
AS
(
    SELECT
        B.ChildPart
        ,B.ParentPart
        ,B.ChildType
        ,1 AS Lvl
        ,B.ChildPart AS StartPart
        ,CASE WHEN B.ChildType NOT IN ('Bought', 'Phantom', 'External') 
            THEN 1 ELSE 0 END AS StopRecursion
    FROM
        @BOM AS B
        INNER JOIN @partsToLook AS P ON P.ChildPart = B.ChildPart

    UNION ALL

    SELECT
        B.ChildPart
        ,B.ParentPart
        ,B.ChildType
        ,CTE.Lvl + 1 AS Lvl
        ,CTE.StartPart
        ,CASE WHEN B.ChildType NOT IN ('Bought', 'Phantom', 'External') 
            THEN 1 ELSE 0 END AS StopRecursion
    FROM
        @BOM AS B
        INNER JOIN CTE ON CTE.ParentPart = B.ChildPart
    WHERE
        CTE.StopRecursion = 0
)
,CTE_RN
AS
(
    SELECT
        StartPart
        ,ParentPart
        ,StopRecursion
        ,ROW_NUMBER() OVER (PARTITION BY StartPart ORDER BY Lvl DESC) AS rn
    FROM CTE
)
SELECT
    StartPart AS ChildPart
    ,CASE WHEN StopRecursion = 1 THEN NULL ELSE ParentPart END AS ExternalParent
FROM CTE_RN
WHERE rn = 1
ORDER BY ChildPart;

Result

+-----------+----------------+
| ChildPart | ExternalParent |
+-----------+----------------+
| a0001     | NULL           |
| a0002     | d0002          |
| a0003     | c0003          |
| a0004     | d0004          |
| a0005     | NULL           |
+-----------+----------------+
Comments