I am using MySQL 5.7 so understand Common Table Expressions are unavailable, but am trying to find a way to build a hierarchy based off a column based input.
For example, my table consists of the following...
| Region | Office | Person |
|---|---|---|
| Region 1 | Office 1 | Employee 1 |
| Region 1 | Office 1 | Employee 2 |
| Region 1 | Office 2 | Employee 1 |
| Region 2 | Office 1 | Employee 1 |
| Region 2 | Office 2 | Employee 1 |
| Region 2 | Office 2 | Employee 2 |
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=049349ecdbf3369026e009dcb08b3c14
The hierarchy should follow the order of the columns in terms of relationship. Region is the root, Office is a node, then person is the leaf / child of office.
The desired output is...
| ID | Item | ParentID |
|---|---|---|
| 1 | Region 1 | 0 |
| 2 | Region 2 | 0 |
| 3 | Office 1 | 1 |
| 4 | Office 1 | 2 |
| 5 | Office 2 | 1 |
| 6 | Office 2 | 2 |
| 7 | Employee 1 | 3 |
| 8 | Employee 1 | 5 |
| 9 | Employee 1 | 4 |
| 10 | Employee 1 | 6 |
| 11 | Employee 2 | 3 |
| 12 | Employee 2 | 6 |
Can someone point me in the right direction of how to achieve this please? I've tried selecting and grouping the region first to give me a single instance,
SELECT Region FROM table GROUP BY Region ORDER BY Region ASC
and then union with the same approach for office, and experimented with dynamically setting the IDs using variables. I've also tried joining the table alias to the given depth number (the level will only ever be 3; Region, Office, Employee) - but I'm stuck!
Any help would be greatly appreciated.
