Hello Friends 
I am back almost after two years. I was busy (lazy) for the past two years to post a new article but an interview that I attended forced me to write this article which could help others who are trying to write recursive queries in SQL Server.
In my previous article, I discussed about writing the recursive query to return organization structure of employees using CTE( common table expression).
In this article I am going to write slightly modified version of the query to return all the managers (hierarchical way. For example, my manger, manger’s manager, his manager etc) of a particular employee which was asked by an interviewer yesterday and I failed to write the same as I had no idea of how CTE works internally till this moment.
Here is the solution.
CREATE TABLE Dept( Dept_id INT PRIMARY KEY, DeptName VARCHAR(100), Loc VARCHAR(100))
GO
INSERT INTO Dept VALUES(101,‘Admin’,‘Chennai’)
INSERT INTO Dept VALUES(102,‘Sales’,‘Hyderabad’)
INSERT INTO Dept VALUES(103,‘Operations’,‘Delhi’)
INSERT INTO Dept VALUES(104,‘IT’,‘Banglore’)
GO
CREATE TABLE Employee(Emp_id INT PRIMARY KEY, EmpName VARCHAR(100),DOJ DATETIME, Salary MONEY, Dept_id INT REFERENCES Dept(Dept_id), Manager_id INT)
GO
INSERT INTO Employee VALUES (101,‘Shivan’,’01-Jan-2005′,20000,101,NULL)
INSERT INTO Employee VALUES (102,‘Ram’,’01-Dec-2006′,15000,103,101)
INSERT INTO Employee VALUES (103,‘Kokila’,’11-Feb-2006′,15000,103,101)
INSERT INTO Employee VALUES (104,‘Jagan’,’21-Aug-2007′,10000,104,102)
INSERT INTO Employee VALUES (105,‘Ela’,’18-Mar-2007′,10000,104,102)
INSERT INTO Employee VALUES (106,‘Kannan’,’16-Jun-2007′,10000,104,103)
INSERT INTO Employee VALUES (107,‘Zerold’,’29-Nov-2007′,10000,104,103)
INSERT INTO Employee VALUES (108,‘Remya’,’23-Apr-2008′,5000,102,104)
INSERT INTO Employee VALUES (109,‘Seetha’,’29-Dec-2008′,5000,102,104)
INSERT INTO Employee VALUES (110,‘Ramanath’,’14-Sep-2008′,5000,102,105)
INSERT INTO Employee VALUES (111,‘Betsy’,’25-Oct-2008′,5000,102,105)
INSERT INTO Employee VALUES (112,‘Sanga’,’07-Mar-2008′,5000,102,106)
INSERT INTO Employee VALUES (113,‘Abhi’,’20-Jan-2008′,5000,102,106)
INSERT INTO Employee VALUES (114,‘Chandra’,’21-Feb-2008′,5000,102,107)
INSERT INTO Employee VALUES (115,‘Vvs’,’11-May-2008′,5000,102,107)
GO
WITH EmpParent(Emp_Id, EmpName, Manager_id) AS
(
SELECT Emp_id, EmpName, Manager_id
FROM Employee
WHERE Emp_id = 109
UNION ALL
SELECT e.Emp_id, e.EmpName, e.Manager_id
FROM Employee e
INNER JOIN EmpParent p
ON e.Emp_id = p.Manager_id
)
SELECT * FROM EmpParent Where Emp_id <> 109
The first query within the CTE references Emp_Id 109 which is the starting record for recursion. This will be joined with second query to get his immediate manager 104 and the same will be passed to the second query recursively to get his immediate manager 102 and so on till the recursion results empty set.
The same query can be modified to list out all the employees under a particular manager. Try it on your own other wise I will be happy to assist.
