I have a table containing Org Unit details for a company with 6 levels of heirarchy. Each lower level record is linked to a superior Org Unit from level 6 upwards. The table columns are structured as follows:
ID (PK) | Reference | Org Unit Level | Org Unit Name | Superior Org Unit Number
NB: 0 is Organisation Level (i.e. top level)
1 | 123456 | 01 | OrgUnitName1 | 0
2 | 345678 | 02 | OrgUnitName2 | 01
3 | 978657 | 03 | OrgUnitName2 | 02
At level 6 there are several hundred different Org Unit categories each of which link up the chain until finally getting to level 0. Another table contains employee details. Each employee record has a Reference number (as described above) against it.
What I need is a query that takes a given reference number from the Employee table (as an input to the query) and returns the Org Unit Name for both the level 2 and level 3 Org Units. I.e. The Reference number is essentially a link to the level 6 Org Unit. I need to move up the tree and return levels 2 and 3.
Hopefully this is possible. Thanks very much.