0 votes
-- Get all descendant products given a parent product key
DECLARE @parentProductKey float = 200539; 
WITH PROD_HIERARCHY
AS
(
       SELECT DISTINCT
             PP1.K_PRODUCT K_PRODUCT_PARENT,
             PP1.K_PRODUCT2 K_PRODUCT_DESCENDANT
       FROM PROD_PROD PP1
             LEFT JOIN PROD_PROD PP2 ON PP1.K_PRODUCT2 = PP2.K_PRODUCT
       WHERE PP1.K_PRODUCT = @parentProductKey
       UNION ALL
       SELECT
             H.K_PRODUCT_PARENT,
             PP.K_PRODUCT2
       FROM PROD_HIERARCHY H
             INNER JOIN PROD_PROD PP ON H.K_PRODUCT_DESCENDANT = PP.K_PRODUCT
)
SELECT * FROM PROD_HIERARCHY

-- Get all ancestor products given a descendant product key
DECLARE @descendantProductKey float = 202099; 
WITH PROD_HIERARCHY AS
(
    SELECT  K_PRODUCT2 K_PRODUCT_DESCENDANT, 
            K_PRODUCT K_PRODUCT_PARENT 
    FROM    PROD_PROD
       WHERE K_PRODUCT2 = @descendantProductKey
    UNION ALL
    SELECT  PP.K_PRODUCT2, 
            PP.K_PRODUCT 
    FROM    PROD_PROD PP
            INNER JOIN PROD_HIERARCHY H ON H.K_PRODUCT_PARENT = PP.K_PRODUCT2
)
SELECT * FROM PROD_HIERARCHY
asked in Database by (224 points)
Some Underscore characters "_" are misinterpreted by overflow :-(
Not if you put it in a code block. I updated your post
It's also mentioned as a recipe in the Efficy Development Cookbook I made years ago, see Efficy document DOC-KPA-2015/10-005307

Please log in or register to answer this question.

1,167 questions
1,425 answers
1,717 comments
325 users