MySql hierarchical query

Question

I have Organizations table:

Id  Name  ParentId
------------------
 1  Org1         5
 2  Org2         5
 3  Org3         4
 4  Depart2      6
 5  Depart1      6
 6  Company   null

What I would like to achieve is query which returns table with belonging of each organization to higher order organization units up in hierarchy tree:

Id    BelongsToOrgId
1     1                  Org1 is part of Org1
1     5                  Org1 is part of Depart1  
1     6                  Org1 is part of Company
2     2                  Org2 is part of Org2
2     5                  Org2 is part of Depart1
2     6                  Org2 is part of Company
3     3                  Org3 is part of Org3
3     4                  Org3 is part of Depart2
3     6                  Org3 is part of Company
4     4                  Depart2 is part of Depart2 
4     6                  Depart2 is part of Company 
5     5                  Depart1 is part of Depart1  
5     6                  Depart1 is part of Company
6     6                  Company is part of Company

Best Regards, Piotr

Akina · Accepted Answer · 2020-09-09 12:56:52Z

2

WITH RECURSIVE
cte AS ( SELECT Id, Name, id BelongsToOrgId, Name UpperName
         FROM Organizations 
       UNION ALL
         SELECT Organizations.Id, Organizations.Name, cte.BelongsToOrgId, cte.Name
         FROM Organizations 
         JOIN cte ON Organizations.ParentId = cte.Id )
SELECT Id, BelongsToOrgId, CONCAT(Name, ' is part of ', UpperName) Relation
FROM cte
ORDER BY Id, BelongsToOrgId;

fiddle

answered Sep 9, 2020 at 12:56

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Comments

piotrassss · Accepted Answer · 2020-09-10 06:21:27Z

0

Thanks Akina,

We are almost there, one fix:

WITH RECURSIVE
cte AS ( SELECT Id, Name, id BelongsToOrgId, Name UpperName
         FROM Organizations 
       UNION ALL
         SELECT Organizations.Id, Organizations.Name, cte.BelongsToOrgId, cte.UpperName
         FROM Organizations 
         JOIN cte ON Organizations.ParentId = cte.Id )
SELECT Id, BelongsToOrgId, CONCAT(Name, ' is part of ', UpperName) Relation
FROM cte
ORDER BY Id, BelongsToOrgId;

It is cte.UpperName and not cte.Name in line 5

answered Sep 10, 2020 at 6:21

piotrassss

3151 gold badge6 silver badges11 bronze badges

Collectives™ on Stack Overflow

MySql hierarchical query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related