CODEWITHSUNDEEP
asp.netsqlsql-serverdatabase
Badr Ajjour
Badr Ajjour

Reputation: 15

sql server query to select parents with specific children

I have the following table : 

ID      Emp_Name     Manager_ID
101     Ken          NULL
102     Terri        101
103     Roberto      101
104     Rob          102
105     Gail         102
106     Jossef       103
107     Dylan        103
108     Diane        105
109     Gigi         105
110     Michael      106

I need to get all Managers of "106 Josef" for example and the result must be : 

106     Josef        103
103     Roberto      101
101     Ken          NULL

what is the best sql server query to do that

Upvotes: 0

Views: 139

Answers (2)

cgonzalez
cgonzalez

Reputation: 46

You can do a while loop to go over the three of managers until you can get the principal node, in each iteration the code insert the row on a temporal table. here is an example.

DECLARE @employeeId  int
DECLARE @managerId int

SET @employeeId=106
SET @managerId=(SELECT Manager_ID FROM Employees WHERE ID=@employeeId)

DECLARE @tableEmployee as TABLE 
(
ID int,
NAME varchar(100),
MANID INT
)
INSERT INTO @tableEmployee SELECT * FROM Employees WHERE ID=@employeeId

WHILE @managerId is not null
BEGIN
INSERT INTO @tableEmployee SELECT * FROM Employees WHERE ID=@managerId
SET @managerId=(SELECT Manager_ID FROM Employees WHERE ID=@managerId)
END
SELECT * FROM @tableEmployee
GO

i think not the best, but works, i hope this helps :).

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415790

My recursive CTE's are very rusty, I don't have a database handy to test with, and this would be easier going the other direction (manager to reportees), but I think this will do it, or at least get close:

declare @EmpID int;
set @EmpId = 106;

with IDs (ID, Manager_ID) As (
     select ID, Manager_ID FROM Employees WHERE ID = @EmpID
     UNION ALL
     SELECT ID, Manager_ID 
     FROM Employees e
     LEFT JOIN ManagerIDs m on e.ID = m.Manager_ID
     WHERE e.ID IS NOT NULL
)
SELECT e.*
FROM IDs i
INNER JOIN Employees e on e.ID = i.ID;

Upvotes: 1

Related Questions