CODEWITHSUNDEEP
sqlsql-serverrecursioncommon-table-expressionrecursive-query
JEPAAB
JEPAAB

Reputation: 166

Conditional Recursive SQL Select

Consider the following database table. It consists of 3 columns: Id, ParentId, Enabled.

Table

I would like to produce a result set similar to the following. Basically for each record that has a Parent ID, I want to to display an additional column Enabled Parent Id. This column basically needs to recursively checks the hierarchy of the key, and stops when a key that is Enabled = True is found.

Result

I would like this to achieve this on the fly, without requiring to add any additional computed columns in the table.

Maybe this could be achieved using a CTE.

Upvotes: 1

Views: 2250

Answers (2)

JEPAAB
JEPAAB

Reputation: 166

DECLARE @myTable TABLE
(
    Id INT NOT NULL,
    ParentId INT NOT NULL,
    EnabledParentId INT
)

DECLARE myCursor CURSOR FOR
SELECT Id
FROM T
WHERE ParentId IS NOT NULL
ORDER BY Id;

OPEN myCursor;

DECLARE @currentId INT;

FETCH NEXT FROM myCursor INTO @currentId;

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @Exists BIT = 0;

    DECLARE @ParentId INT               
    SELECT @ParentId = ParentId
    FROM T
    WHERE Id = @currentId

    WHILE (@ParentId IS NOT NULL AND @Exists = 0)
    BEGIN                   

        IF EXISTS(SELECT * FROM T WHERE Id = @ParentId AND IsEnabled = 1)
        BEGIN

            SET @Exists = 1             

        END
        ELSE
        BEGIN

                SELECT @ParentId = ParentId
                FROM T
                WHERE Id = @ParentId

        END

        IF (@Exists = 1 OR @ParentId IS NULL)
        BEGIN
            INSERT INTO @myTable
            SELECT Id, ParentId, @ParentId
            FROM T
            WHERE Id = @currentId       
        END



    END

   FETCH NEXT FROM myCursor INTO @currentId;
END

CLOSE myCursor;
DEALLOCATE myCursor;

SELECT *
FROM @myTable
ORDER BY 1

Upvotes: 0

valex
valex

Reputation: 24144

Try this CTE query:

WITH T1 as 
(SELECT id,
        parentId,
        NULL as EnabledParentId, 
        ParentID as NextParent 
        FROM T
        WHERE ParentID is not null
UNION ALL
SELECT T1.id, 
       T1.parentId, 
       CASE WHEN T.enabled = 1 
            THEN T.ID 
            ELSE NULL END 
              as EnabledParentId,
       T.ParentID as NextParent
 FROM T1 
 JOIN T ON T1.NextParent = T.Id
 WHERE (nextParent is not Null) and (EnabledParentId IS NULL)
)
SELECT ID,
       ParentID,
       EnabledParentID 
       FROM T1 
       WHERE EnabledParentId IS NOT NULL 
             OR NextParent IS NULL
       ORDER BY ID;

Upvotes: 2

Related Questions