pejman - 1 year ago 76
SQL Question

# Find children recursively

I have a table like this:

Creation script:

``````CREATE #TableName TABLE (
Id int,
Id_Group 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?

Recursive CTE could do thing for you:

``````DECLARE @var int = 1014

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

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download