List records from a chosen employee to the ultimate manager-CodePudding

Given the following Table (In Postgres):

EmployeeID	EmployeeName	ManagerID
1	David	NULL
2	Jessica	NULL
3	Gregg	1
4	Joe	3
5	Brandon	NULL
6	Leslie	4
7	Harry	6
8	Paul	NULL
9	Frank	5

Starting from Employee ID 7 (Harry) - how do I list only the records, bottom-up, in the hierarchy until I get to the ultimate manager (which should be David, in this case)? Should I use recursive CTE? If so, how does that query look?

Expected Output:

EmployeeID	EmployeeName	ManagerID
1	David	NULL
3	Gregg	1
4	Joe	3
6	Leslie	4
7	Harry	6

CodePudding user response：

In the common table expression, you can recognize:

the base step, where you select the record relatively to employee no 7
the recursive step, where you match managers with currently retrieved employees.

The recursion stops when the INNER JOIN does not return any more extra record.

WITH RECURSIVE cte AS (
    SELECT * FROM tab WHERE EmployeeID = 7
  
    UNION ALL
  
    SELECT mgr.*
    FROM       cte emp
    INNER JOIN tab mgr
            ON emp.ManagerID = mgr.EmployeeID
)
SELECT * 
FROM cte

Check the demo here.