MariaDB recursive query – 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;