CODEWITHSUNDEEP
sqlsql-servertree
Anatolyevich
Anatolyevich

Reputation: 701

SQL Server : build valid tree filtering invalid branches

I have a table with following data:

ID     ParentID    Name
-----------------------
 1       NULL      OK1
 2        1        OK2
 3        2        OK3
 5        4        BAD1
 6        5        BAD2

So I need to take only those lines, which are linked to ParentID = NULL OR valid children of such lines (i.e: OK3 is valid because it's linked to OK2, which is linked to OK1, which is linked to NULL, which is valid.)

But BAD1 and BAD 2 are not valid because those are not linked to a line, which is linked to NULL.

The best solution I figured out is a procedure + function. And function is called as many times as the max number of link levels in the table.

Can anybody suggest better solution for such task?

Upvotes: 0

Views: 70

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

All you need is love, and a basic recursive CTE :-)

Create and populate sample data (Please save us this step in future questions):

DECLARE @T as table
(
    ID int,
    ParentID int,
    Name varchar(4)
)

INSERT INTO @T VALUES
(1, NULL, 'OK1'),
(2, 1, 'OK2'),
(3, 2, 'OK3'),
(5, 4, 'BAD1'),
(6, 5, 'BAD2')

The CTE and query:

;WITH CTE AS
(
    SELECT ID, ParentId, Name
    FROM @T 
    WHERE ParentId IS NULL

    UNION ALL

    SELECT T1.ID, T1.ParentId, T1.Name
    FROM @T T1
    INNER JOIN CTE T2 ON T1.ParentID = T2.ID
)

SELECT *
FROM CTE

Results:

ID          ParentId    Name
----------- ----------- ----
1           NULL        OK1
2           1           OK2
3           2           OK3

Upvotes: 2

Related Questions