How to create a query with all of dependencies in hierarchical organization?

Question

I've been trying hard to create a query to see all dependencies in a hierarchical organization. But the only I have accuaried is to retrieve the parent dependency. I have attached an image to show what I need.

Thanks for any clue you can give me.

enter image description here

This is the code I have tried with the production table.

WITH CTE AS
    (SELECT
        H1.systemuserid,
        H1.pes_aprobadorid,
        H1.yomifullname,
        H1.internalemailaddress
    FROM [dbo].[ext_systemuser] H1
    WHERE H1.pes_aprobadorid is null
    UNION ALL 
    SELECT
        H2.systemuserid,
        H2.pes_aprobadorid,
        H2.yomifullname,
        H2.internalemailaddress           
    FROM [dbo].[ext_systemuser] H2
    INNER JOIN CTE c ON h2.pes_aprobadorid=c.systemuserid)
SELECT *
FROM CTE 
OPTION (MAXRECURSION 1000)

score 0 · Answer 1 · answered Jul 18 '19 at 13:23

You are almost there with your query. You just have to include all rows as a starting point. Also the join should be cte.parent_id = ext.user_id and not the other way round. I've done an example query in postgres, but you shall easily adapt it to your DBMS.

with recursive st_units as (
  select 0 as id, NULL as pid, 'Director' as nm
  union all select 1, 0, 'Department 1'
  union all select 2, 0, 'Department 2'
  union all select 3, 1, 'Unit 1'
  union all select 4, 3, 'Unit 1.1'
),
cte AS
(
SELECT id, pid, cast(nm as text) as path, 1 as lvl
FROM st_units

UNION ALL
SELECT c.id, u.pid, cast(path || '->' || u.nm as text), lvl + 1
FROM st_units as u
  INNER JOIN cte as c on c.pid = u.id
)
SELECT id, pid, path, lvl
FROM cte
ORDER BY lvl, id

id |  pid | path                                     | lvl
-: | ---: | :--------------------------------------- | --:
 0 | null | Director                                 |   1
 1 |    0 | Department 1                             |   1
 2 |    0 | Department 2                             |   1
 3 |    1 | Unit 1                                   |   1
 4 |    3 | Unit 1.1                                 |   1
 1 | null | Department 1->Director                   |   2
 2 | null | Department 2->Director                   |   2
 3 |    0 | Unit 1->Department 1                     |   2
 4 |    1 | Unit 1.1->Unit 1                         |   2
 3 | null | Unit 1->Department 1->Director           |   3
 4 |    0 | Unit 1.1->Unit 1->Department 1           |   3
 4 | null | Unit 1.1->Unit 1->Department 1->Director |   4

db<>fiddle here

score 0 · Answer 2 · answered Jul 22 '19 at 06:40

I've reached this code that it is working but when I include a hierarchy table of more than 1800 the query is endless.

With cte AS
(select systemuserid, systemuserid as pes_aprobadorid, internalemailaddress, yomifullname
from @TestTable
union all
SELECT c.systemuserid, u.pes_aprobadorid, u.internalemailaddress, u.yomifullname
FROM @TestTable as u
INNER JOIN cte as c on c.pes_aprobadorid = u.systemuserid
)
select distinct * from cte
where pes_aprobadorid is not null
OPTION (MAXRECURSION 0)

How to create a query with all of dependencies in hierarchical organization?

2 Answers2