I've an EMPLOYEE table which has built-in hierarchy structure as well (using manager column)
I've another REGION table which represents manager-region relation
I'm trying to create a SQL which will show which employees fall under which region by following up the hierarchy chain.
Constraints / Rules:
- It's possible that employee's immediate manager may not have a region - so I need to continue up the chain.
-
I have guarantee that 4 level up the chain someone will have a region.
-
If region is found before the 4th level then use lower manager's region
This is the naive SQL I came up with (but results have duplicates - 3rd Rule failed)
select distinct e.name, r.region
from employee e
left outer join employee mgr1 on mgr1.id = e.manager
left outer join employee mgr2 on mgr2.id = mgr1.manager
left outer join employee mgr3 on mgr3.id = mgr2.manager
left outer join employee mgr4 on mgr4.id = mgr3.manager
left outer join REGION r on
( r.id = mgr1.id
or r.id = mgr2.id
or r.id = mgr3.id
or r.id = mgr4.id )
where e.IS_MANAGER = 'N'; //only interested in users for now; assume a flag
And this is the ResultSet:
How do I conditionally stop the left outer join if I already found a region?
Aucun commentaire:
Enregistrer un commentaire