pejman pejman - 2 months ago 12
SQL Question

Find children recursively

I have a table like this:

enter image description here
Creation script:

CREATE #TableName TABLE (
Id int,
Id_Group int,
Id_Menu int
)
INSERT INTO #TableName VALUES
(1, 20005, 1014),(2, 20003, 1054),(3, 20005, 1058),
(4, 20004, 1055),(5, 20004, 1056),(6, 20004, 1057),
(7, 20003, 1053),(8, 1014, 0),(9, 20003, 1014),
(10, 20003, 1052),(11, 20003, 1164),(12, 20003, 1065),
(13, 20003, 1066),(14, 20003, 1067),(15, 20003, 1068),
(16, 20004, 1014),(17, 50100, 20003),(18, 50200, 20003),
(19, 50100, 20004),(20, 50200, 20004),(21, 50100, 20005),
(22, 50200, 20005)


I get
Id_Menu
for example
1014
it must find the records which its
id_menu
must equal
1014
, the result is
20003, 20004, 20005
and for result list I must find all records which its
id_menu
s are
20003, 20004, 20005
etc.

How can I do this?

Answer

Recursive CTE could do thing for you:

DECLARE @var int = 1014

;WITH rec AS (
SELECT  Id, 
        Id_Group, 
        Id_Menu,
        1 as [level]
FROM YourTable
WHERE Id_Group = @var
UNION ALL
SELECT  y.Id,
        y.Id_Group,
        y.Id_Menu,
        r.[level]+1
FROM YourTable y
INNER JOIN rec r
    ON r.Id_Group = y.Id_Menu
)

SELECT *
FROM rec

Output:

Id  Id_Group    Id_Menu level
8   1014        0       1
1   20005       1014    2
9   20003       1014    2
16  20004       1014    2
19  50100       20004   3
20  50200       20004   3
17  50100       20003   3
18  50200       20003   3
21  50100       20005   3
22  50200       20005   3