MariaDB recursive query

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

idtitleparent_id
1CEONULL
2Director 11
3Director 21
4Manager 12
5Manager 22
6Manager 33
7Manager 43
8OP 14
9OP 24
10OP 35
11OP 45
12OP 56
13OP 66
14OP 77
15OP 87

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).

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/

Please follow and like us:
Pin Share