SQL Recursive CTE Lookup in Hierarchy to get a Part Only Flat BOM

Question

I am trying to create a part only flat level Puchase/Manufacturing BOM from a Hierarchal Engineering BOM. the Database is set up with a PARTMASER table the lists all partnumberss and a BOMLEGER table that has the PARENT, CHILD columns for all assemblies. I am using the following code to get my flat BOM

WITH cteBuildPath AS
   (
   --=== This is the "anchor" part of the recursive CTE.
     -- The only thing it does is load the Root Node.
      SELECT ROOT.PARENT_PARTMASTER_CODE, ROOT.CHILD_PARTMASTER_CODE, ROOT.BOMLEGER_QTY
       FROM BOMLEGER ROOT
       WHERE ROOT.PARENT_PARTMASTER_CODE LIKE '%10550-03A%'--The Root Node
    UNION ALL
     --==== This is the "recursive" part of the CTE that adds 1 for each level
     -- and concatenates each level of EmployeeID's to the SortPath column.
      SELECT 
            PARENT.PARENT_PARTMASTER_CODE, 
            --CHILD.PARENT_PARTMASTER_CODE as SUBPARENT,
            CHILD.CHILD_PARTMASTER_CODE, 
            CONVERT(decimal(6,2),PARENT.BOMLEGER_QTY*CHILD.BOMLEGER_QTY)
       FROM 
            cteBuildPath PARENT, 
            BOMLEGER CHILD
       WHERE PARENT.CHILD_PARTMASTER_CODE = CHILD.PARENT_PARTMASTER_CODE
   )
   --=== This final SELECT/INTO creates the Node # in the same order as a
     -- push-stack would.
SELECT PARENT_PARTMASTER_CODE AS PARENT, 
--SUBPARENT,
CHILD_PARTMASTER_CODE AS CHILD, 
SUM(BOMLEGER_QTY) AS "Total QTY"

 FROM cteBuildPath
  GROUP BY PARENT_PARTMASTER_CODE, CHILD_PARTMASTER_CODE
  ORDER BY PARENT_PARTMASTER_CODE, CHILD_PARTMASTER_CODE;

and I get the result

PARENT	CHILD	Total QTY
10550-03A	CMP-0000003	2.00
10550-03A	HARD-0000816	8.00
10550-03A	HARD-0000817	8.00
10550-03A	HARD-0000834	24.00
10550-03A	HARD-0000835	24.00
10550-03A	HARD-0000840	24.00
10550-03A	HARD-0000866	6.00
10550-03A	HARD-0000868	8.00
10550-03A	HARD-0000872	4.00
10550-03A	HARD-0000874	4.00
10550-03A	HARD-0001047	4.00
10550-03A	HARD-0001103	8.00
10550-03A	MECH-0001409	4.00
10550-03A	PLT-0000131	2.00
10550-03A	PLT-0000132	1.00
10550-03A	PLT-0000133	4.00
10550-03A	PLT-0000134	1.00
10550-03A	PLT-0000135	1.00
10550-03A	PLT-0000136	1.00
10550-03A	PLT-0000137	1.00
10550-03A	PLT-0000138	4.00
10550-03A	PLT-0000139	2.00
10550-03A	PLUMB-0001378	2.00
10550-03A	PUR-0000003	4.00
10550-03A	PUR-0000004	1.00
10550-03A	PUR-0000005	4.00
10550-03A	PUR-0000006	2.00
10550-03A	STR-0000138	4.00
10550-03A	STR-0000139	2.00
10550-03A	STR-0000140	4.00
10550-03A	STR-0000141	4.00
10550-03A	STR-0000142	1.00
10550-03A	STR-0000143	1.00
10550-03A	STR-0000144	2.00
10550-03A	STR-0000145	1.00
10550-03A	STR-0000146	1.00
10550-03A	STR-0000147	1.00
10550-03A	STR-0000148	2.00
10550-03A	STR-0000149	2.00
10550-03A	STR-0000150	2.00
10550-03A	STR-0000151	2.00
10550-03A	STR-0000152	1.00
10550-03A	STR-0000153	2.00
10550-03A	STR-0000154	2.00
10550-03A	STR-0000155	4.00
10550-03A	STR-0000156	2.00
10550-03A	SUB-0000018	1.00
10550-03A	WLD-0000120	1.00
10550-03A	WLD-0000122	1.00
10550-03A	WLD-0000123	1.00
10550-03A	WLD-0000124	1.00
10550-03A	WLD-0000125	2.00

The issue is I am getting all the mid sub-assemblies in my result and I don't want them. So the SUB-0000018 is a midlevel Subassembly with children so since the Children are in the output I dont want the Subassembly.

Answer 1

You can just add a NOT EXISTS to the final SELECT to check if it's the bottom level part.

WITH cteBuildPath AS
(
   --=== This is the "anchor" part of the recursive CTE.
     -- The only thing it does is load the Root Node.
      SELECT
        ROOT.PARENT_PARTMASTER_CODE,
        ROOT.CHILD_PARTMASTER_CODE,
        ROOT.BOMLEGER_QTY
      FROM BOMLEGER ROOT
      WHERE ROOT.PARENT_PARTMASTER_CODE LIKE '%10550-03A%'--The Root Node

    UNION ALL

     --==== This is the "recursive" part of the CTE that adds 1 for each level
     -- and concatenates each level of EmployeeID's to the SortPath column.
    SELECT 
        PARENT.PARENT_PARTMASTER_CODE, 
        CHILD.CHILD_PARTMASTER_CODE,
        CONVERT(decimal(6,2), PARENT.BOMLEGER_QTY * CHILD.BOMLEGER_QTY)
    FROM 
      cteBuildPath PARENT
    JOIN BOMLEGER CHILD
        ON PARENT.CHILD_PARTMASTER_CODE = CHILD.PARENT_PARTMASTER_CODE
)
   --=== This final SELECT/INTO creates the Node # in the same order as a
     -- push-stack would.
SELECT
  PARENT_PARTMASTER_CODE AS PARENT, 
  CHILD_PARTMASTER_CODE AS CHILD, 
  SUM(BOMLEGER_QTY) AS "Total QTY"
FROM cteBuildPath bp
WHERE NOT EXISTS (SELECT 1
    FROM BOMLEDGER_ROOT child
    WHERE child.PARENT_PARTMASTER_CODE = bp.CHILD_PARTMASTER_CODE
)
GROUP BY
  PARENT_PARTMASTER_CODE,
  CHILD_PARTMASTER_CODE
ORDER BY
  PARENT_PARTMASTER_CODE,
  CHILD_PARTMASTER_CODE;