Join repeatedly until a string is present?

While querying a subset of our employees, I’m trying to add a field for the SVP they “roll up” to.

Employees may have anywhere from 1 to 5 or 6 degrees of separation from their SVP. The trouble is, we don’t have any specific hierarchical indicator to reference. I have to do this by walking up through the employee’s manager repeatedly, until some manager’s manager has “SVP” in their title.

How could I write a query to do this?

From the opposite direction, I’ve found the employees of a specific SVP (named BM for the example) by saying, ‘Employee’s manager is BM, OR Employee’s manager’s manager is BM, OR Employee’s manager’s manager’s manager is BM’ and so on…

For my instance, I suspect I’d only use the same sys_user table over and over again, following the manager field each time until I reach a user with SVP in the title.

+--------+-------------------+-----------+--------+
| sys_id |      name         | title     | manager|
+--------+-------------------+-----------+--------+
| 555789 | Tina Belcher      | Contractor| 123456 | 
| 123456 | Bob Belcher       | Manager   | 654321 |
| 654321 | Calvin Fischoeder | SVP       | 997755 |
+--------+-------------------+-----------+--------+

SELECT su.Name
     , su.Title
     , dp.name
     , mg.name

FROM sys_user                   su
    LEFT JOIN cmn_department    dp
        ON dp.sys_id = su.department
    LEFT JOIN sys_user          mg
        ON mg.sys_id = su.manager

WHERE su.Title like ('%contractor%')

I appreciate any helps or tip that can be offered. Thanks for looking and have a great day.

Answer

Your Sys_User table is an adjacency list that only provides information about employees and who they directly report to. Adjacency Lists are one way to to encode hierarchical data. They’re nice because they’re relatively fast and compact, however they aren’t the only way to encode hierarchical relationships.

To answer the questions you are asking you will benefit from re-encoding the data into a Closure table which maps each employee to all of its direct and indirect managers/reportees along with their degree of separation, and any other additional pertinent info. However, since it represents a Many-to-Many relationship you don’t want to over load it with too much additional data. Fortunately thanks to the utility of recursive queries you can create one fairly easily on the fly.

To create a closure table you start by populating it with the degree 0 relationships, where every employee is considered their own manager/reportee. The reasoning for it is a bit beyond my ken, but it has something to do with the mathematics behind the concept of transitive closure (hence the name closure table). After that you iteratively (recursively) add each additional reporting degree. You can either do that from the Top Down, or from the Bottom Up

Here’s the Top Down version:

with closure(manager_id, report_id, degree, is_managing_SVP, is_reporting_svp) as (
  select sys_id
       , sys_id
       , 0
       , case when title like '%SVP%' then 1 else 0 end
       , case when title like '%SVP%' then 1 else 0 end
    from sys_user
  union all
  select cur.manager_id
       , nxt.sys_id
       , cur.degree+1
       , cur.is_managing_SVP
       , case when nxt.title like '%SVP%' then 1 else 0 end
    from closure cur
    join sys_user nxt
      on nxt.manager = cur.report_id
     and nxt.sys_id <> nxt.manager
)
select * from closure

And here’s the Bottom Up version:

with closure(manager_id, report_id, degree, is_managing_SVP, is_reporting_svp) as (
  select sys_id
       , sys_id
       , 0
       , case when title like '%SVP%' then 1 else 0 end
       , case when title like '%SVP%' then 1 else 0 end
    from sys_user
  union all
  select nxt.manager
       , cur.report_id
       , cur.degree+1
       , case when mgr.title like '%SVP%' then 1 else 0 end
       , cur.is_reporting_SVP
    from closure cur
    join sys_user nxt
      on nxt.sys_id = cur.manager_id
     and nxt.sys_id <> nxt.manager
    join sys_user mgr
      on mgr.sys_id = nxt.manager
)
select * from closure

It doesn’t matter too much which version you use if you are going to generate the entire closure table, however, if you want to optimize your query and only generate a partial closure table then it depends on if you want to walk up or down the tree.

Once generated you can use the closure table to answer your questions about SVPs such as who each contractor’s SVP is:

select r.sys_id, r.name, r.title, c.degree
     , c.manager_id SVP_ID
     , m.name SVP_name
     , m.title SVP_title
  from sys_user r
  join closure c
    on c.report_id = r.sys_id
  join sys_user m
    on m.sys_id = c.manager_id
 where r.title like '%contractor%'
   and c.is_managing_svp = 1

sys_id | name         | title      | degree | SVP_ID | SVP_name          | SVP_title
-----: | :----------- | :--------- | -----: | -----: | :---------------- | :--------
555789 | Tina Belcher | Contractor |      2 | 654321 | Calvin Fischoeder | SVP      

Or every direct and indirect report to the SVP named Calvin Fischoeder:

select m.sys_id manager_id
     , m.name
     , m.title
     , c.degree
     , r.sys_id report_id
     , r.name report_name
     , r.title report_title
  from sys_user m
  join closure c
    on c.manager_id = m.sys_id
  join sys_user r
    on r.sys_id = c.report_id
 where m.name = 'Calvin Fischoeder'
 order by degree, report_name

manager_id | name              | title | degree | report_id | report_name       | report_title
---------: | :---------------- | :---- | -----: | --------: | :---------------- | :-----------
    654321 | Calvin Fischoeder | SVP   |      0 |    654321 | Calvin Fischoeder | SVP         
    654321 | Calvin Fischoeder | SVP   |      1 |    123456 | Bob Belcher       | Manager     
    654321 | Calvin Fischoeder | SVP   |      2 |    555789 | Tina Belcher      | Contractor  

To see all queries in action, check out this db<>fiddle