Manager-Managed classic self-join table

Question

Is there a way to get to the root of the hierarchy with a single SQL statement?

The significant columns of the table would be: EMP_ID, MANAGER_ID.

MANAGER_ID is self joined to EMP_ID, as manager is also an employee. Given an EMP_ID is there a way to get to the employee (manager) (walking up the chain) where EMP_ID is null?

In other words the top guy in the org?

I'm using SQL Server 2008

Thanks.

i assume you have an unbalanced tree here. my suggestion would be implementing a function manager(emp_id) that will go up the chain entirely. that way you could `select emp_id, manager(emp_id) from yourtable`. — Brett Schneider, Nov 15 '13 at 14:53
You might want to read this SO question and answer: http://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join-in-sql-server — Igor Korkhov, Nov 15 '13 at 15:26

score 0 · Answer 1 · answered Nov 15 '13 at 15:21

0

It's hard to find a single SQL query that will bring the result with the current structure you have for your table. Like Brett said, you can try with a stored function.

But what I think is best worth looking at is nested sets, which is one well-confirmed design for trees implemented in relational databases.

answered Nov 15 '13 at 15:21

Andrei Nicusan

4,555
1
23
36

"It's hard to find a single SQL query that will bring the result with the current structure". No, it is not. See this SO question (and answer), for instance: http://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join-in-sql-server – Igor Korkhov Nov 15 '13 at 15:24

score 0 · Accepted Answer · answered Nov 15 '13 at 17:11

0

You want a Common Table Expression. Among other things, they can do recursive queries just like what you're looking for.

answered Nov 15 '13 at 17:11

thyme

480
7
16

score 0 · Answer 3 · answered May 03 '18 at 23:57

This is an old question but I had to recently do the same thing so I want to give an example of CTE expresion for this question:

Assuming having a table:

EMP_ID MANAGER_ID  
1 NULL  
2 1  
3 NULL  
4 2  
5 3  
6 5  
7 3

CTE expresion starts with finding root levels which have MANAGER_ID filled with nulls and completing it with the rest of employees with simple inner join to root level

WITH PARENT
AS (
SELECT EMP_ID,
      MANAGER_ID,
      EMP_ID as BOSS
FROM tab
WHERE MANAGER_ID IS NULL
UNION ALL
SELECT T.EMP_ID,
       T.MANAGER_ID,
       PARENT.BOSS
FROM PARENT
    INNER JOIN TAB T ON T.MANAGER_ID = PARENT.EMP_ID)
SELECT *
FROM parent;

This gives as result:

EMP_ID MANAGER_ID BOSS
1 NULL 1
3 NULL 3
5 3 3
7 3 3
6 5 3
2 1 1
4 2 1

Manager-Managed classic self-join table

3 Answers3