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?

Answer 1

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.