Get hierarchy of all different level of managers

Question

I'm using pgAdmin4 and I have a SQL table with employee/manager HR data that looks like this:

| employee_id | email_address  | full_name   | band_lvl | manager_id |
| 5592        | jillr@ex.org   | Jill Rhode  | 20       | 6521       |
| 6421        | racheln@ex.org | Rachel Nam  | 40       | 4251       |
| 2818        | todda@ex.org   | Todd Alex   | 25       | 6421       |
| 4251        | jalens@ex.org  | Jalen Smith | 60       | 2199       |
| 6521        | tolun@ex.org   | Tolu Nagoye | 30       | 2199       |
| 7831        | jina@ex.org    | Ji Na       | 80       | NULL       |
| 2199        | zaynm@ex.org   | Zayn Mate   | 70       | 7831       |

Based on the first manager_id and employee_id, I'm seeking to return the following columns: Level1 Manager Name, Level1 Manager Email, Level1 Manager Band Lvl, Level1 Manager Manager's Id. I then want to do that for each manager that's a step above, until there are no higher managers.

The desired output should look like this:

| employee_id | email_address  | full_name   | band_lvl | manager_id | Lvl1 Mng Nm | Lvl1 Mng Email | Lvl1 Mng Band Lvl | Lvl1 Mng Mngs Id | Lvl2 Mng Nm    | Lvl2 Mng Email | Lvl2 Mng Band Lvl | Lvl2 Mng Mngs Id | 
| 5592        | jillr@ex.org   | Jill Rhode  | 20       | 6521       | Tolu Nagoye | tolun@ex.org   | 30                | 2199             | Zayn Mate      | zaynm@ex.org   | 70                | 7831             |
| 6421        | racheln@ex.org | Rachel Nam  | 40       | 4251       | Jalen Smith | jalens@ex.org  | 60                | 2199             | Zayn Mate      | zaynm@ex.org   | 70                | 7831             |
| 2818        | todda@ex.org   | Todd Alex   | 25       | 6421       | Rachel Nam  | racheln@ex.org | 40                | 4251             | Jalen Smith    | jalens@ex.org  | 60                | 2199             |
| 4251        | jalens@ex.org  | Jalen Smith | 60       | 2199       | Zayn Mate   | zaynm@ex.org   | 70                | 7831             | Ji Na          | jina@ex.org    | 80                | NULL             | 
| 6521        | tolun@ex.org   | Tolu Nagoye | 30       | 2199       | Zayn Mate   | zaynm@ex.org   | 70                | 7831             | Ji Na          | jina@ex.org    | 80                | NULL             |
| 7831        | jina@ex.org    | Ji Na       | 80       | NULL       | NULL        | NULL           | NULL              | NULL             | NULL           | NULL           | NULL              | NULL             |
| 2199        | zaynm@ex.org   | Zayn Mate   | 70       | 7831       | Ji Na       | jina@ex.org    | 80                | NULL             | NULL           | NULL           | NULL              | NULL             |

So far, this is what I've come up with, to get the first columns for the Level 1 Manager; however, I don't know where to go from here, as I'm very new to SQL:

SELECT B.employee_id,
        B.email_address, 
        B.full_name, 
        B.band_lvl,
        B.manager_id, 
        B1.full_name AS L1_mng_nm, 
        B1.email_address AS L1_mng_email, 
        B1.band_lvl AS L1_mng_band_lvl,
        B1.manager_id AS L1_mgr_mgrs_id
FROM hrdata B
INNER JOIN hrdata B1 ON
        B.manager_id = B1.employee_id;

Welcome to Stackoverflow. FYI - I removed conflicting platform tags. SQL Server <> MySQL, they're different products. Please feel free to edit your question and (re)add a tag for the platform you are using. — squillman, Apr 26 '22 at 13:49
@HariHaravelan thank you for the note, I just updated my question with the RDBMS I'm using, which is pgAdmin4 — John Adeniran, Apr 27 '22 at 05:08

score 0 · Answer 1 · answered Apr 26 '22 at 14:41

Your query is close, but you would need to make a few changes to get to your desired output. To begin, I would recommend doing a LEFT JOIN as opposed to an INNER JOIN, as the INNER JOIN will not return null values and will instead drop records that it cannot find a match for in both tables (in this case, if it cannot find a match on manager_id to employee_id from the first use of hrdata to the second use of hrdata).

After that, your query should look similar to what you have already done, just with another self-join to get the second-level manager data:

SELECT B.employee_id,
       B.email_address, 
       B.full_name, 
       B.band_lvl,
       B.manager_id, 
       B1.full_name AS L1_mng_nm, 
       B1.email_address AS L1_mng_email, 
       B1.band_lvl AS L1_mng_band_lvl,
       B1.manager_id AS L1_mgr_mgrs_id,
       B2.full_name AS L2_mng_nm, 
       B2.email_address AS L2_mng_email, 
       B2.band_lvl AS L2_mng_band_lvl,
       B2.manager_id AS L2_mgr_mgrs_id,
FROM hrdata B
LEFT JOIN hrdata B1
       ON B1.employee_id = B.manager_id
LEFT JOIN hrdata B2
       ON B2.employee_id = B1.manager_id

Thank you so much for this helpful response! I'm messing around now with my query to adjust; I was wondering if you had any insights on how to make this recursive? I'm trying to see how I can get data up until the nth-level manager — John Adeniran, Apr 26 '22 at 15:22
Glad to help! I would recommend checking into [Recursive CTEs](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15) for dynamically finding the hierarchy in your case--there are also some additional [StackOverflow answers that you may find useful](https://stackoverflow.com/a/41634263/13885000) — cdbullard, Apr 26 '22 at 18:16

Get hierarchy of all different level of managers

1 Answers1