I have a SQL Server table with a parent-child hierarchy for parts (no children) and kits (with children) in an inventory. An example of the data would be something like:

In this product, there is a TLA, SKU1, and a structure. I’d like to recursively query this product, returning all descendants given the TLA number, but with a condition: if [isStockedAsKit] is true, I only want to show the parent of that kit, and no descendants.

The goal is to get a resulting table that looks like this:

This is my current query (which grabs ALL descendants):

WITH level AS 
(
    SELECT Parent,Child,0 as level_number 
    FROM [Bundles] 
    WHERE Child = 'SKU1' 
    
    UNION ALL 
    
    SELECT child.Parent,child.Child,level_number+1 as level_number 
    FROM [Bundles] as child 
    JOIN level as l on l.Child = child.Parent
) 
SELECT DISTINCT 
    level_number AS 'Lvl', l.Parent, l.Child, b.isStockedAsBundle
FROM 
    level AS l 
JOIN 
    [Bundles] AS b ON l.ID = b.ID

>Solution :

So include isStockedAsKit in your query and check it when joining with Bundles:

WITH level AS 
(
    SELECT id, Parent,Child,0 as level_number, isStockedAsKit 
    FROM [Bundles] 
    WHERE Child = 'SKU1' 
    
    UNION ALL 
    
    SELECT child.Id,child.Parent,child.Child,level_number + 1 as level_number, 
           child.isStockedAsKit 
    FROM [Bundles] as child 
    JOIN level as l on l.Child = child.Parent and l.isStockedAsKit = 'false'
) 

SELECT DISTINCT level_number as 'Lvl', l.Parent, l.Child, l.isStockedAsKit
from level l

dbfiddle demo