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!

Alex Spitz · Accepted Answer · 2021-06-05 09:14:30Z

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;

answered Jun 5, 2021 at 9:14

Alex Spitz

858 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lucasparmentier388 Over a year ago

First of all, thank you. I didn't expect such a quick response. Since I'm not at home to test, does this sound right to you: paste.ubuntu.com/p/HXrPSmcCV9?

Alex Spitz Over a year ago

@lucasparmentier388 I think it would look like what you wrote. Though I would have to try it to be sure. You''re on the good path :)

Gordon Linoff · Accepted Answer · 2021-06-05 11:55:45Z

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.

answered Jun 5, 2021 at 11:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Simple recursive SQL query

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related