How To Get A Hierarchical CTE In SQL Server To Filter With Parent and Child Logic

Question

I'm having a vexing problem with a hierarchical CTE and some strange logic that we need to address that I really hope someone could assist with pointing out what I'm doing wrong to address this scenario with a CTE.

Here is the hierarchical data we're dealing with in this example:

This is the problematic SQL followed by the description of the problem and SQL statements to create a test table with data:

    DECLARE @UserId nvarchar(50);
    SET @UserId = 'A';

    DECLARE @StatusType int;
    SET @StatusType = '2';

     ;WITH recursiveItems (Id, Depth)
     AS
     (
        SELECT Id, 0 AS Depth 
        FROM dbo.CteTest 
        WHERE UserId = @UserId 
                    --AND StatusType = @StatusType
                    -- This would also be incorrect for the issue
        AND ParentId IS NULL
        UNION ALL
        SELECT dbo.CteTest.Id, Depth + 1 
        FROM dbo.CteTest 
            INNER JOIN recursiveItems 
                ON dbo.CteTest.ParentId = recursiveItems.Id
        WHERE UserId = @UserId 
        AND StatusType = @StatusType
     )

    SELECT A.*, recursiveItems.Depth
    FROM recursiveItems
    INNER JOIN dbo.CteTest A WITH(NOLOCK) ON
        recursiveItems.Id = A.Id
        ORDER BY A.Id

This is not returning the desired data. The data that is currently returned is in the NOT CORRECT section of the image below. The row with the Id of 10 is the row that we want to omit.

Essentially the logic should be that any parent record (record with children) where the status type of any of its children is equal to 2 should be returned along with its children. In the example this is the rows with Ids: 1, 5, 6, 7, 9.

Currently the CTE/SQL/Code is returning ALL parent records no matter what,

The record with the Id 1 should be returned, even though it's status type is 1 because at least one of its children, their children, grandchildren, etc. have a status type that is equal to 2.

The record with the Id of 10 should not be returned because it does not have a status that is equal to 2 or any children. If the record had a status type of 2 when it has no child records it should also be returned.

This is the DDL to create a test table that helps to show the problem:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CteTest](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StatusType] [int] NOT NULL,
[UserId] [nvarchar](50) NOT NULL,
[ParentId] [int] NULL,
 CONSTRAINT [PK_CteTest] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,         ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

This is the seed data for the table, that can demonstrate the issue:

INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (1,'A',NULL)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (1,'B',NULL)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (2,'B',NULL)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (1,'A',1)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (2,'A',1)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (2,'A',5)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (2,'A',6)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (3,'A',6)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (2,'A',NULL)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (4,'A',NULL)
INSERT INTO [dbo].[CteTest]([StatusType],[UserId],[ParentId]) VALUES (3,'A',10)

Answer 1

The issue is that your base case includes all null (parentless) items, and there is no way to filter them out later.

Because you are looking for only items with a particular statustype, you may want to refactor the CTE; Instead of having a base case be the root values, you can have it be all items with the given statustype, and then recursively find the parents. In the solution below, I have depth be a negative number, for distance from the item with a value of 2 in the given tree (so negative height, instead of depth.).

DECLARE @UserId nvarchar(50);
SET @UserId = 'A';

DECLARE @StatusType int;
SET @StatusType = '2';

WITH recursiveItems (Id, ParentID, Depth)
 AS
 (
    SELECT Id, ParentID, 0 AS Depth 
    FROM dbo.CteTest 
    WHERE UserId = @UserId AND StatusType = @StatusType
    UNION ALL
    SELECT dbo.CteTest.Id, CteTest.ParentID, Depth - 1 
    FROM dbo.CteTest 
        INNER JOIN recursiveItems 
            ON dbo.CteTest.Id = recursiveItems.ParentId
    WHERE UserId = @UserId 
 )
     SELECT A.Id, A.StatusType, A.UserId, A.ParentId, min(recursiveItems.Depth)
FROM recursiveItems
INNER JOIN dbo.CteTest A WITH(NOLOCK) ON
    recursiveItems.Id = A.Id
    group by A.Id, A.StatusType, A.UserId, A.ParentId
    ORDER BY A.Id