Zulqadar Idrishi

Zulqadar Idrishi

  • 1.5k
  • 445
  • 25k

CTE to get all children and nested children of every parent

Apr 8 2021 7:23 AM
I'm stuck in a problem that continuously hammering in my mind to find the solution.
 
I have a users table with 6k of rows and they are related as parent-child. The problem is that I want results not only at a single level parent-child relationship but at all levels (until the last child) for every userid.
 
I have this data in my users table.
 
Tbl_Users
UserID Parent ID
1 0
2 1
3 1
4 2
5 2
6 5
7 6
8 6
9 0
 
I want this data as the output of CTE.
 
Output
Parent ID UserID
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
2 2
2 4
2 5
2 6
2 7
2 8
3 3
4 4
5 5
5 6
5 7
5 8
6 6
6 7
6 8
7 7
8 8
9 9
 
Note: The ParentID includes not only its children but also children of its children and itself too. I'm using MSSQL 2019. 

Answers (2)