sql server query to select parents with specific children

Question

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

Check this post. Its close to what you are looking for : http://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server — Chandu, Oct 04 '13 at 23:08
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Oct 04 '13 at 23:11
Study this one: http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/ — PM 77-1, Oct 04 '13 at 23:21

score 1 · Answer 1 · answered Oct 04 '13 at 23:24

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;

I tried this query but the result does not match Expected result — Badr Ajjour, Oct 04 '13 at 23:39

score 1 · Accepted Answer · answered Oct 05 '13 at 05:23

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 :).

sql server query to select parents with specific children

2 Answers2