MariaDB – Marko Medojević

Database table org_structure stores an organizational structure nested hierarchy. It starts from the root (CEO) which has parent_id NULL.

id	title	parent_id
1	CEO	NULL
2	Director 1	1
3	Director 2	1
4	Manager 1	2
5	Manager 2	2
6	Manager 3	3
7	Manager 4	3
8	OP 1	4
9	OP 2	4
10	OP 3	5
11	OP 4	5
12	OP 5	6
13	OP 6	6
14	OP 7	7
15	OP 8	7

This is a graphical representation of org_structure database table.

Let us take an example that we want to select “Director 2” and every descendant in the hierarchy. So result will include (Director 2, Manager 3, Manager 4, OP 5 – 8).

MariaDB provides elegant solution for this problem (recursive queries).

with recursive res (id, title) as (
  select id, title 
  from org_structure
  where id = 3
  union all
  select os.id, os.title 
  from org_structure os
  inner join res
  on os.parent_id = res.id
)
select * from res;

with recursive res (id, title) as (

select id, title

from org_structure

where id = 3

union all

select os.id, os.title

from org_structure os

inner join res

on os.parent_id = res.id

)

select * from res;

Result:

3 Director 2
6 Manager 3
7 Manager 4
12 OP 5
13 OP 6
14 OP 7
15 OP 8

Detailed explanation can be found on following link:
https://mariadb.com/kb/en/recursive-common-table-expressions-overview/

Category: MariaDB

MariaDB recursive query