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
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 |
+--------+----------+------------+
you can use this simple query SELECT * FROM EMP_TABLE WHERE MANAGER_ID IS NOT NULL
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;