Who Reports to Whom?
Kestrel Labs is migrating to a new HR system, and the vendor wants the reporting structure as a two-column spreadsheet: each person, and the person they report to. Ids are no use to them — the import file has to carry names on both sides.
The catch is that a manager is an employee too. There is no separate table of managers to look things up in; Ava Chen is row 1 as a person and is also the answer to "who does Ben Ortiz report to". So the same ten rows have to play two parts at once, once as the person and once as the boss.
employees — one row per person. manager_id holds the id of another row in this same table, or is NULL for someone who reports to nobody.
| id | name | department_id | salary | hire_date | manager_id |
|---|---|---|---|---|---|
| 1 | Ava Chen | 1 | 145000 | 2021-03-14 | NULL |
| 2 | Ben Ortiz | 1 | 118000 | 2022-06-01 | 1 |
| 3 | Cara Novak | 1 | 121000 | 2023-01-10 | 1 |
| 4 | Deshawn Lee | 2 | 95000 | 2020-09-23 | NULL |
| 5 | Elin Kask | 2 | 88000 | 2022-11-05 | 4 |
| 6 | Farid Amiri | 3 | 76000 | 2023-04-18 | NULL |
| 7 | Grace Kim | 4 | 132000 | 2021-07-30 | NULL |
| 8 | Hugo Silva | 4 | 110000 | 2023-02-14 | 7 |
| 9 | Ines Duarte | 4 | 104000 | 2023-08-01 | 7 |
| 10 | Jonas Weber | 2 | 91000 | 2021-12-19 | 4 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning two columns, name and manager_name, with one row for every employee who has a manager. The four people whose manager_id is NULL do not appear at all — not with an empty manager, not at all. Rows come back in alphabetical order of the employee's own name.
Example output — shape only, on an invented company; a manager of two people shows up once beside each of them.
| name | manager_name |
|---|---|
| Lena Fournier | Anton Reis |
| Mira Kovac | Anton Reis |
| Yusuf Bello | Rosa Kwan |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in