Find All employees reporting to manager, including managers employees

Question

I have a table with at least three columns, EMPLID, NAME, SUPERVISOR_ID. In a single query, I'd like get a managers direct reports including any direct reports from subordinate mangers all the way down to the lowest staff when given a managersid in a WHERE clause.

I can create a query that will show who reports to who:

    SELECT MANAGER.[EMPLID] AS MANAGEREMPLID, MANAGER.[NAME], MANAGER.[SUPERVISOR_ID], STAFF.[EMPLID] AS STAFFEMPLID, STAFF.[NAME], STAFF.[SUPERVISOR_ID]  
  FROM MYHRTABLE AS MANAGER INNER JOIN MYHRTABLE AS STAFF
  ON MANAGER.[EMPLID] = STAFF.[SUPERVISOR_ID]
  ORDER BY MANAGER.[NAME], STAFF.[NAME]

or recursive CTE that seemingly does the same thing

 WITH MYCTE
AS ( 
SELECT [EMPLID], [NAME],[SUPERVISOR_ID] FROM 
    (SELECT [EMPLID], [NAME], 
        CASE WHEN [EMPLID] = [SUPERVISOR_ID] THEN NULL ELSE [SUPERVISOR_ID] END AS [SUPERVISOR_ID]              
    FROM MYHRTABLE) AS MYDATA
        WHERE [SUPERVISOR_ID] IS NULL

UNION ALL

    SELECT MYDATA.[EMPLID], MYDATA.[NAME],MYDATA.[SUPERVISOR_ID] FROM 
    (SELECT [EMPLID], [NAME], 
        CASE WHEN [EMPLID] = [SUPERVISOR_ID] THEN NULL ELSE [SUPERVISOR_ID] END AS [SUPERVISOR_ID]      
    FROM MYHRTABLE) AS MYDATA INNER JOIN MYCTE ON MYDATA.[SUPERVISOR_ID] = MyCTE.[EMPLID]
    WHERE MYDATA.[SUPERVISOR_ID] IS NOT NULL)
SELECT * FROM MyCTE

(the case statement is because the highest manager has the supervisor field reporting to herself)

What I was looking for was the ability to give this query a emplid and see all the way down to staff who reports to that manager. In my two examples, I feel like I can see direct reports, but somehow I should do another join to see the rest of the information.

And a second question kind of related, while researching this, it seems most people solve finding a managers direct reports using a recursive cte when I think it could have been solved using the first query joined to itself. Given the two queries I posted, why would I use a recursive CTE to solve it when I could use the same table joined to itself? It seems like I get the same information back?

If it means anything, this will be run on SQL Server 2012 or 2008.

Answer 1

The reason you use recursion is to be able to drill down in the data by recursively changing to manager used as anchor until there are nothing more to find and get the output as a hierarchy.

The join query would be limited to one level per join (try adding a where manager.emplid = 1 (or w/e the id of the manager is) to it and observe what happens). If you know the number of levels in advance you could use left joins (with one join per level).

I would alter the recursive solution to this:

with src as (
  select 
    [emplid]
    , [name]
    , case 
        when [emplid] = [supervisor_id] then null 
        else [supervisor_id] 
     end as [supervisor_id]
  from myhrtable
), cte as ( 
  select [emplid], [name], [supervisor_id], [emplid] as top_manager
  from src
  where [emplid] = 1 -- this is the manager you're interested in
                     -- it can be a sub-manager in the middle or the tree
union all            -- or the top level manager rooting the tree

  select src.[emplid], src.[name], src.[supervisor_id], top_manager
  from src 
  inner join cte on src.[supervisor_id] = cte.[emplid]
  where src.[emplid] <> top_manager
)

select * from cte