Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CTE parent-child showing siblings

I have a CTE-query that displays a tree using recursion. This works great when displaying the whole tree. But I want to pass in ID as a variable and include the siblings for each current node.

Testcode:

DECLARE @TT TABLE 
(
ID int,
Name varchar(25),
ParentID int,
SortIndex int
)

INSERT @TT 
SELECT 1, 'A', NULL, 1 UNION ALL
SELECT 2, 'B_1', 3, 1 UNION ALL
SELECT 3, 'B', 1, 2 UNION ALL
SELECT 4, 'B_2', 3, 2 UNION ALL
SELECT 5, 'C', 1, 3 UNION ALL
SELECT 6, 'C_2', 5, 2  UNION ALL
SELECT 7, 'A_1', 1, 1 UNION ALL
SELECT 8, 'A_2', 1, 2 UNION ALL
SELECT 9, 'C_1', 5, 1 


;WITH CTETree
AS
(
    SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 1 AS Lev,
    CAST(ROW_NUMBER() OVER(ORDER BY SortIndex) AS VARBINARY(MAX)) AS SortPath
    FROM @TT
    WHERE ParentID IS NULL

    UNION ALL

    SELECT F.*, CTETree.Name AS ParentName, Lev + 1,
    SortPath + CAST(ROW_NUMBER() OVER(ORDER BY F.SortIndex) AS BINARY(32))
    FROM @TT AS F
    INNER JOIN CTETree
    ON F.ParentID = CTETree.ID 
)

SELECT * FROM CTETree
    ORDER BY SortPath

/*
DESIRED RESULT:

WHEN ID = 3 PASSED IN:

1   A   NULL    1   NULL    1
3   B   1   2   A   2
2   B_1 3   1   B   3
4   B_2 3   2   B   3
5   C   1   3   A   2

WHEN ID = 1 PASSED IN:

1   A   NULL    1   NULL    1
3   B   1   2   A   2
5   C   1   3   A   2

WHEN ID = 9 PASSED IN:

1   A   NULL    1   NULL    1
3   B   1   2   A   2
5   C   1   3   A   2
9   C_1 5   1   C   3
6   C_2 5   2   C   3

*/

SQL-Fiddle: http://sqlfiddle.com/#!3/d41d8/5526

like image 776
Lasse Edsvik Avatar asked Nov 03 '22 13:11

Lasse Edsvik


1 Answers

Only in once I confront a question where disappear A_1 and A_2. Therefore add in exception(in case clause). If you need this records delete this expression AND f.ID != 7 AND f.ID != 8.

DECLARE @ID int = 3 -- variable wich you want pass
DECLARE @Matched int = (SELECT CASE WHEN ParentID = 1 THEN ID ELSE ParentID END FROM @TT WHERE ID = @ID)
;WITH CTETree
AS
(
 SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 1 AS Lev,
        CAST(ROW_NUMBER() OVER(ORDER BY SortIndex) AS VARBINARY(MAX)) AS SortPath,
        0 AS Matched
 FROM @TT
 WHERE ParentID IS NULL
 UNION ALL
 SELECT F.*, cte.Name AS ParentName, cte.Lev + 1,
        SortPath + CAST(ROW_NUMBER() OVER(ORDER BY F.SortIndex) AS BINARY(32)),
        CASE WHEN (cte.ID = 1 AND f.ID != 7 AND f.ID != 8)
        OR (cte.ID = @Matched AND cte.Lev + 1 > 2)
        THEN 1 END AS Matched
 FROM @TT AS F INNER JOIN CTETree cte
 ON F.ParentID = cte.ID
)
SELECT ID, Name, ParentID, SortIndex, ParentName, Lev FROM CTETree
WHERE Matched IS NOT NULL
ORDER BY SortPath

Demo on SQLFiddle

like image 200
Aleksandr Fedorenko Avatar answered Nov 13 '22 14:11

Aleksandr Fedorenko