Find all employee overseers

Question

I know that the answer seems to be using "WITH RECURSIVE" according to this post , but I just don't get it.

I have a table called people and a table called position_hierarchy . The people table has a unique identifier uperson_id and a position identifier, which we call pcn and the enabled flag (because when someone leaves and is replaced, the same pcn gets their replacement). position_hierarchy has a pcn column and another pcn column, which is the pcn person located above them in the hierarchy. What I want to do is give the person uperson_id and find all the uperson_id people above them in the hierarchy, and / or give uperson_id and the other person uperson_id and tell whether the second person is the one who has a supervisory position over the first.

The company president is indicated because their pcn matches their reports_to . (Not my solution - I would use null reports_to )

So far I have come to the following:

 with recursive parents (uperson_id, pcn, reports_to) as ( select p1.uperson_id, ph1.pcn, ph1.reports_to from people p1 join position_hierarchy ph1 on ph1.pcn = p1.pcn where reports_to != ph1.pcn and active_revoke_flag = '0' union all select p2.uperson_id, ph2.pcn, ph2.reports_to from people p2 join position_hierarchy ph2 on p2.pcn = ph2.pcn join parents pp on pp.pcn = ph2.reports_to ) select parents.* from parents where uperson_id = 'aaa3644'; 

but it returns 5 lines with the same uperson_id, pcn and reports_to (which seems like the correct number of lines, but I want the uperson_id supervision at every level. I feel like I have something very simple, I probably hit my head when you tell me what I'm doing wrong.

What I've done

Based on Erwin Brandstetter's answer, I fixed several things (mainly because I didn’t active_revoke_flag out which table the active_revoke_flag was active_revoke_flag ), and came up with

 with recursive p as ( select pcn, reports_to from position_hierarchy where pcn = (SELECT pcn FROM people WHERE uperson_id = 'aaa3644') union all select ph2.pcn, ph2.reports_to from p join position_hierarchy ph2 ON ph2.pcn = p.reports_to AND p.pcn != p.reports_to ) select p2.uperson_id, p2.active_revoke_flag, p.* from p join people p2 USING (pcn) where p2.active_revoke_flag = '0'; 

Answer 1

I would try this approach from the bottom up, start with the person you are interested in and start your journey:

 with recursive p as ( select p1.uperson_id, p1.pcn, ph1.reports_to from people p1 join position_hierarchy ph1 USING (pcn) where ph1.active_revoke_flag = '0' and p1.uperson_id = 'aaa3644' union all select p2.uperson_id, p2.pcn, ph2.reports_to from p join position_hierarchy ph2 ON ph2.pcn = p.reports_to AND ph2.active_revoke_flag = '0' join people p2 ON p2.pcn = ph2.pcn ) select * from p; 

Or faster, because we only join person once:

 with recursive p as ( select pcn, reports_to from position_hierarchy where active_revoke_flag = '0' and pcn = (SELECT pcn FROM person WHERE uperson_id = 'aaa3644') union all select ph2.pcn, ph2.reports_to from p join position_hierarchy ph2 ON ph2.pcn = p.reports_to AND ph2.active_revoke_flag = '0' ) select p2.uperson_id, p.* from p join people p2 USING (pcn); -- assuming pcn is unique in table person 

Aside: I find your duplicate pcn somewhat dubious.

Answer 2

If you know the maximum depth of a tree, you can destroy it in one request; otherwise, you need to read Joe Chelco and rebuild your tables.

One request for a maximum depth of 5 may look like this (untested):

  select distinct ph1.pcn
 from position_hierarchy ph1
 join position_hierarchy ph2 on ph1.pcn = ph2.reports_to
 left join position_hierarchy ph3 on ph2.pcn = ph3.reports_to
 left join position_hierarchy ph4 on p3.pcn = ph4.reports_to
 left join position_hierarchy ph5 on ph4.pcn = ph5.reports_to
 where ph2.pcn = @my_pcn
 or ph3.pcn = @my_pcn
 or ph4.pcn = @my_pcn
 or ph5.pcn = @my_pcn;

The result will be a pcn list of all @my_pcn bosses. You can also add something that needs to be checked for an emergency president of the company.

Chelko wrote a book about hierarchies in SQL, literally: http://books.google.ca/books/about/Joe_Celko_s_Trees_and_Hierarchies_in_SQL.html?id=Jy4e8SbYO6sC