recursive query

How does one write a recursive query?  This would come into play where a table is self-referencing.   Like, a list of employees, where there is a primary Key and supervisor column, where the latter maps to the primary key.   So, If I want a list of employees everyone from person "X" on down, how do I do this?
LVL 1
HLRosenbergerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Ephraim WangoyaConnect With a Mentor Commented:
here is an example

ID Parent
1  null
2  1
3  1
4  null
5  4
6  5

with CTE(ID, PARENT)
AS
(
      SELECT F.ID, F.PARENT
      FROM TABLE1 F
      WHERE PARENT IS NULL
      UNION ALL
      SELECT F.ID, F.PARENT
      FROM TABLE1 F
      INNER JOIN CTE FH ON FH.ID = F.PARENT
)

SELECT ID, PARENT FROM CTE
0
 
Ephraim WangoyaCommented:

You can use a recursive Common Table Expression (CTE) to do this kind of query
0
 
gdupadhyayCommented:
I believe you are looking to make a tree for employee:
See example below:
ID: Emp ID
Name: Emp Name
Manager: Emp. Manager ID (NULL: Emp. have no manager)

ID      Name      Manager
1      a      NULL
2      b      1
3      c      1
4      X      2
5      Y      2
6      Z      1
7      P      3

WITH Employee_Tree (ID,
		Name,
		Manager,
		Depth) AS(
    SELECT  ID,
		Name,
		Manager,
		1 AS depth
     FROM Employee WITH (NOLOCK)
     WHERE MANAGER IS NULL
   UNION ALL
	SELECT  N.ID,
	        N.Name,
		N.Manager,
		ET.Depth + 1
		FROM Employee AS N WITH (NOLOCK)
	INNER JOIN Employee_Tree ET 
		ON ET.ID = N.Manager)
		SELECT ID,
		   Name,
		   Manager,
	FROM Employee_Tree

Open in new window

0
 
HLRosenbergerAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.