SQL Recursive query using CTE to filter on multiple columns

Question

I'm trying to feed the output of query 1 as the input for query 2. In order to do this I'm using CTE(Common Table Expressions).

Employee Hierarchy

DEPT     ID     MANAGER1     MANAGER2     MANAGER3
Sales    A234   10389               19400               3049
HR       F394   29489               20403               Null
IT       H339   A234
IT       S599   39009               A234

Expected output

DEPT     ID     MANAGER1     MANAGER2     MANAGER3
IT       H339   A234
IT       S599   39009               A234

Problem: Identify direct reports of managers in Sales department. in the above example ID 234 is from Sales department. His direct reports are in IT since A234 is under DIRECT_MANAGER1 and DIRECT_MANAGER2.

WITH CTE AS(
SELECT ID from Employee_Hierarchy
WHERE DEPT='Sales'
)
SELECT DEPT, ID 
FROM Employee_Hierarchy

But I do not know the join condition here for CTE

Answer 1

I don’t think you need recursion for this. It seems like a correlated subquery is enough:

select e.*
from employee_hierarchy e
where exists(
    select 1
    from employee_hierarchy m
    where 
        m.id in (e.manager_id1, e.manager_id2, e.manager_id3)
        and m.dept = 'Sales'
)