CODEWITHSUNDEEP
sqlpostgresqlrecursionpostgresql-9.5
Old Fox
Old Fox

Reputation: 8725

Postgresql recursion return an unexpected result

I'm trying to retrieve the entire employees hierarchy of specific manager.

The table is as the following:

id :: int
manager :: foreign key(users)

Data:

| id  | manager | 
-----------------
| 103 | 138     |
| 125 | 138     |
| 114 | 103     |
| 122 | 103     |
| 138 | NULL    |
| 144 | 222     |
| 222 | NULL    |

When I execute the following recursion:

WITH RECURSIVE managed AS (
        SELECT manager AS manager_id, id AS employee_id
        FROM users
        WHERE manager = 138
    UNION 
        SELECT u2.manager AS manager_id, u2.id as employee_id
        FROM users u2 
        INNER JOIN managed m ON m.employee_id = u2.id
    )


select *
from managed;

I received wrong result:

enter image description here

While the expected is:(exactly as the below link show...)

| employee_id  | manager_id | 
-----------------------------
| 103          | 138        |
| 125          | 138        |
| 114          | 103        |
| 122          | 103        |

I tried many examples include :: this one, which is similar to my question, but none of them worked...

When I tried to execute the query with UNION ALL the execution never ends(after several minutes I had to cancel the execution)

Thanks in advance :)

Upvotes: 1

Views: 76

Answers (2)

Roman Tkachuk
Roman Tkachuk

Reputation: 3266

You have small error - you need join on m.employee_id = u2.manager not m.employee_id = u2.id:

WITH RECURSIVE managed AS (
        SELECT manager AS manager_id, id AS employee_id
        FROM users
        WHERE manager = 138
    UNION 
        SELECT u2.manager AS manager_id, u2.id as employee_id
        FROM users u2 
        INNER JOIN managed m ON m.employee_id = u2.manager
    )


select *
from managed;

Result is:

 manager_id | employee_id 
------------+-------------
        138 |         103
        138 |         125
        103 |         114
        103 |         122
(4 rows)

Upvotes: 1

Gab
Gab

Reputation: 3520

Here is how to find the employees hierarchy:

WITH managers AS (
    SELECT u.id
    FROM users u
    WHERE exists (SELECT 1 FROM users WHERE manager = u.id LIMIT 1)
)
SELECT u.id as employee_id, m.id as manager_id
FROM users u
JOIN managers m ON m.id = u.manager;

->

 employee_id | manager_id 
-------------+------------
         122 |        103
         114 |        103
         103 |        138
         125 |        138
 (4 rows)

Upvotes: 0

Related Questions