Simple recursive SQL query

Question

I will need to perform a recursive SQL query to retrieve all subordinates of a superior X from the following subordination table:

superior_id	subordinate_id
1080	1070
1080	1060
1070	1020
1070	1030
1060	1040

The subordinates of the subordinates of superior X are therefore his subordinates too.

I didn't really understand how I could do that by looking at the documentation. That's why I'm asking for your help on one of the simple examples to implement it.

If it helps, my database engine is PostgreSQL, although I think in this case it doesn't change much.

Thanks in advance for your help!

Answer 1

For your data model, the logic is:

with recursive cte as (
      select subordinate_id
      from subordinates
      where superior_id = X
      union all
      select s.subordinate_id
      from cte join
           subordinates s
           on s.superior_id = cte.subordinate_id
     )
select subordinate_id
from cte;

Here is a db<>fiddle.

The first part of the recursive CTE is the anchor subquery. It returns all rows that are direct subordinates of X. The second part is the recursive portion. It is joining back to the CTE based on the hierarchical relationship.

Answer 2

I think you can find your answer here: Postgresql recursive self join

Then this website is quite good to explain stuff like that: https://www.postgresqltutorial.com/postgresql-recursive-query/

Here is an example with an employee / subordinate model:

WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = 2
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
    *
FROM
    subordinates;