SQL Server : conditional parent-child recursive query

dxxyhpgq 于 2023-06-21 发布在 SQL Server

关注(0)|答案(1)|浏览(176)

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:
| Lvl | Parent | Child | isStockedAsKit |
| ------------ | ------------ | ------------ | ------------ |
| 0 | NULL | SKU1 | false |
| 1 | SKU1 | SKU3 | false |
| 1 | SKU1 | SKU4 | true |
| 2 | SKU4 | SKU2 | false |
| 2 | SKU4 | SKU5 | false |
| 1 | SKU1 | SKU7 | false |
| 2 | SKU7 | SKU11 | false |
| 2 | SKU7 | SKU12 | false |

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:
| Lvl | Parent | Child | isStockedAsKit |
| ------------ | ------------ | ------------ | ------------ |
| 0 | NULL | SKU1 | false |
| 1 | SKU1 | SKU3 | false |
| 1 | SKU1 | SKU4 | true |
| 1 | SKU1 | SKU7 | false |
| 2 | SKU7 | SKU11 | false |
| 2 | SKU7 | SKU12 | false |

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 
    level_number AS 'Lvl', l.Parent, l.Child, b.isStockedAsBundle
FROM 
    level AS l 
JOIN 
    [Bundles] AS b ON l.ID = b.ID

sql-server

来源：https://stackoverflow.com/questions/76424967/sql-server-conditional-parent-child-recursive-query

1条答案

按热度按时间

3pmvbmvn1#

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

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server : conditional parent-child recursive query

1条答案

相关问题

热门标签

最新问答