samedi 25 avril 2015

How to remove duplicates in a complicated JOIN


I've an EMPLOYEE table which has built-in hierarchy structure as well (using manager column)

EMPLOYEE table

I've another REGION table which represents manager-region relation

REGION table

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:

RESULTS

How do I conditionally stop the left outer join if I already found a region?


Aucun commentaire:

Enregistrer un commentaire