sql query get all children and grandchildren

Question

What is proper way to get all children and grandchildren or n-generation children. for children and grandchildren my query is like below:

SELECT e1.ID, e1.LName + ', '+e1.FName
  FROM lstEmp e1  INNER JOIN lstEmp e2 ON e1.Supervisor = e2.ID
  Where e2.Supervisor = 'xxxxxxx'
 UNION 
  SELECT distinct e2.ID, e2.LName + ', '+e2.FName
  FROM lstEmp e1  INNER JOIN lstEmp e2 ON e1.Supervisor = e2.ID
  Where e2.Supervisor = 'xxxxxxx'

my question:1)is this the proper way? 2) what if I need all children and grandchildren and (4,5..)n-generation children?

Can you make a Sql fiddle? [sqlfiddle](http://sqlfiddle.com/) — , Jul 11 '14 at 13:53
Regarding getting grand-children: It depends on what database you are using (and the versions). Many newer databases support recursive table expressions, and some Oracle database support the `CONNECT BY` keyword. — Jonny, Jul 11 '14 at 13:59
A recursiveCTE is about the best way to do this with the current structure. — Sean Lange, Jul 11 '14 at 14:18
I can show you if can post ddl (create table statements) and sample data (insert statements). You can do this here or create a fiddle as already suggested. Of course our old friend google has TONS of examples. :) — Sean Lange, Jul 11 '14 at 14:23

score 1 · Accepted Answer · answered Jul 11 '14 at 14:27

The following CTE starts with the row where ID = 'xxx'. Then, its children and grandchildren are selected by joining the ID with the Supervisor.

WITH grandchildren as
(
    SELECT e1.ID ID, e1.LName + ', '+e1.FName FullName, 0 as lvl
    FROM lstEmp e1
    WHERE e1.ID ='xxx'
    UNION ALL
    SELECT e2.ID, e2.LName + ', '+e2.FName, lvl+1
    FROM lstEmp e2  
    INNER JOIN grandchildren g ON e2.Supervisor = g.LsuID
)
SELECT *
FROM grandchildren
ORDER BY lvl, ID;

Here's a great reference on TechNet. There is a good example of selecting Managers and Direct Reports.

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

If you can change the table schema, I'd say the best way to handle this is to use a HierarchyId data type in your table which is designed for exactly the problem you're having. See the documentation here http://msdn.microsoft.com/en-gb/library/bb677290.aspx and here http://msdn.microsoft.com/en-us/library/bb677173.aspx for more info on that.

If you can't change the table, I would use a cursor within a stored procedure and return the results that way - Good example here: T-SQL Stored Procedure for Looping a hierarchy with recursion

edited May 23 '17 at 10:32

Community

1
1

answered Jul 11 '14 at 14:02

broken_manc

1

2

Ugh!!! A cursor is about the slowest method I can think of for this with the exception of a while loop. The HierarchyID would work but even better would be to use nested sets. – Sean Lange Jul 11 '14 at 14:16

sql query get all children and grandchildren

2 Answers2