SQL Query to get name of all employees under a manager recursively-CodePudding

I am looking for a query to get name of all employees under a particular manager recursively.

EMP_TABLE

EMP_ID     EMP_NAME     MANAGER_ID
1          A            3
2          B            3
3          C            4
4          D            5
5          E            NULL

Query:

SELECT EMP_ID, EMP_NAME, MANAGER_ID FROM EMP_TABLE WHERE MANAGER_ID=4;

Output of this query:

EMP_ID     EMP_NAME     MANAGER_ID
3          C            4

Expected output:

EMP_ID     EMP_NAME     MANAGER_ID
1          A            3
2          B            3
3          C            4

CodePudding user response：

You need to use CONNECT BY to process hierarchical data in the table:

https://docs.snowflake.com/en/sql-reference/constructs/connect-by.html

SELECT EMP_ID, EMP_NAME, MANAGER_ID FROM EMP_TABLE 
START WITH MANAGER_ID=4
CONNECT BY MANAGER_ID = prior EMP_ID
ORDER BY EMP_ID;

 -------- ---------- ------------ 
| EMP_ID | EMP_NAME | MANAGER_ID |
 -------- ---------- ------------ 
|      1 | A        |          3 |
|      2 | B        |          3 |
|      3 | C        |          4 |
 -------- ---------- ------------

CodePudding user response：

The CONNECT BY solution is very clear.

Here you have a recursive solution:

WITH rec AS (
    SELECT EMP_ID, EMP_NAME, MANAGER_ID 
      FROM EMP_TABLE 
     WHERE MANAGER_ID=4
     UNION ALL
    SELECT tb.EMP_ID, tb.EMP_NAME, tb.MANAGER_ID 
      FROM rec AS ta
      JOIN EMP_TABLE AS tb ON ta.EMP_ID = tb.MANAGER_ID
)
SELECT EMP_ID, EMP_NAME, MANAGER_ID 
  FROM rec
 ORDER BY 1;

CodePudding user response：

you can use this simple query SELECT * FROM EMP_TABLE WHERE MANAGER_ID IS NOT NULL