Pull subsequent values-CodePudding

So let's say I have the following table

 Employee_ID Supervisor_ID
 001         123
 178         123
 002         178
 154         178
 003         154         
 004         201

I'm attempting to pull all employees that fall under the supervision of Supervisor_ID 123 along with all other supervisors within that branch (ie 178, 158) so it would spit out the following employees 001, 178, 002, 154, 003. How would I achieve this ? My apologies for the lack of wording in the title.

CodePudding user response：

We can use a recursive hierarchical query here:

WITH cte AS (
    SELECT Employee_ID, Supervisor_ID
    FROM yourTable
    WHERE Employee_ID = 123
    UNION ALL
    SELECT t1.Employee_ID, t1.Supervisor_ID
    FROM yourTable t1
    INNER JOIN cte t2
        ON t2.Employee_ID = t1.Supervisor_ID
)

SELECT Employee_ID
FROM cte
WHERE Employee_ID <> '123'
ORDER BY Employee_ID;

Demo

Note that your sample table seems to be lacking an actual record where employee 123 appears as an employee. My query above assumes that such a record would be present.