Recursive Cte While Parent Id Not In A List

I have the following Nested Set That results in this tree I have a list of produts SELECT Id, Name ... FROM Products A many-to-many relationship with the categories. All Categori

Solution 1:

Note: I edited slightly to reflect the sample data you provided.

The Setup

Here is what I have to represent your nested set:

declare@nestedSettable (
    id int,
    parentId int
);

insert@nestedSetvalues 
    (1, null), (2, 1), (4, 2), (7, 4), (8, 4), (10, 1), (9, 10), (1004, 1),
    (3, null), (5, 3), (6, 3),
    (13, null), (11, 13), (12, 13);

Here is what I built for your promotions:

declare@promotionstable (
    promotionId intidentity(1,1),
    categoryId int,
    price float
);

insert@promotionsvalues (1, 5), (2, 15), (3, 10);

And your products, which I have renamed productCategories to better reflect its contents:

declare@productCategoriestable (productId int, categoryId int);
insert@productCategoriesvalues (1,7),(1,8),(1,6);

The Solution

As an anchor, I just pulled in the products table. But I think in your use case you'll want a filter to pick out the appropriate base products. Then I did a calculation to check to see if the category was already a promotion. If it was, then it represented a leaf node.

In the recursion, I simply moved up the hierarchy of nested set for every node that was not a leaf. I again did the calculation to see if the category was a promotion to see if it was a leaf node or not.

From the results, I selected all leaf nodes, ordered by price, and output the top one.

declare@productIdint=1;

with

    traverse as (

        select      categoryId, 
                    parentId, 

                    isLeaf = iif(exists (
                        select0from@promotions pm 
                        where pd.categoryId = pm.categoryId
                    ), 1, 0)

        from@productCategories pd
        join@nestedSet n on pd.categoryId = n.id
        where       pd.productId =@productIdunionallselect      categoryId = par.id,
                    par.parentId,

                    isLeaf = iif(exists (
                        select0from@promotions pm 
                        where par.id = pm.categoryId
                    ), 1, 0)

        from        traverse pd
        join@nestedSet par on pd.parentId = par.id
        where       pd.isLeaf =0

    )


    select      
    top 1       p.*from        traverse t
    join@promotions p on t.categoryId = p.categoryId
    where       isLeaf =1orderby    p.price