'Get hierarchy of all different level of managers

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        | [email protected]   | Jill Rhode  | 20       | 6521       |
| 6421        | [email protected] | Rachel Nam  | 40       | 4251       |
| 2818        | [email protected]   | Todd Alex   | 25       | 6421       |
| 4251        | [email protected]  | Jalen Smith | 60       | 2199       |
| 6521        | [email protected]   | Tolu Nagoye | 30       | 2199       |
| 7831        | [email protected]    | Ji Na       | 80       | NULL       |
| 2199        | [email protected]   | 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        | [email protected]   | Jill Rhode  | 20       | 6521       | Tolu Nagoye | [email protected]   | 30                | 2199             | Zayn Mate      | [email protected]   | 70                | 7831             |
| 6421        | [email protected] | Rachel Nam  | 40       | 4251       | Jalen Smith | [email protected]  | 60                | 2199             | Zayn Mate      | [email protected]   | 70                | 7831             |
| 2818        | [email protected]   | Todd Alex   | 25       | 6421       | Rachel Nam  | [email protected] | 40                | 4251             | Jalen Smith    | [email protected]  | 60                | 2199             |
| 4251        | [email protected]  | Jalen Smith | 60       | 2199       | Zayn Mate   | [email protected]   | 70                | 7831             | Ji Na          | [email protected]    | 80                | NULL             | 
| 6521        | [email protected]   | Tolu Nagoye | 30       | 2199       | Zayn Mate   | [email protected]   | 70                | 7831             | Ji Na          | [email protected]    | 80                | NULL             |
| 7831        | [email protected]    | Ji Na       | 80       | NULL       | NULL        | NULL           | NULL              | NULL             | NULL           | NULL           | NULL              | NULL             |
| 2199        | [email protected]   | Zayn Mate   | 70       | 7831       | Ji Na       | [email protected]    | 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;
sqlpgadmin-4


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 cdbullard

Related Questions