Anatolyevich Anatolyevich - 5 months ago 12
SQL Question

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?

Answer

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
Comments