Table: Employees
+---------------+---------+ | Column Name | Type | +---------------+---------+ | employee_id | int | | employee_name | varchar | | manager_id | int | +---------------+---------+ employee_id is the column of unique values for this table. Each row of this table indicates that the employee with ID employee_id and name employee_name reports his work to his/her direct manager with manager_id The head of the company is the employee with employee_id = 1.
Write a solution to find employee_id of all employees that directly or indirectly report their work to the head of the company.
The indirect relation between managers will not exceed three managers as the company is small.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Employees table: +-------------+---------------+------------+ | employee_id | employee_name | manager_id | +-------------+---------------+------------+ | 1 | Boss | 1 | | 3 | Alice | 3 | | 2 | Bob | 1 | | 4 | Daniel | 2 | | 7 | Luis | 4 | | 8 | Jhon | 3 | | 9 | Angela | 8 | | 77 | Robert | 1 | +-------------+---------------+------------+ Output: +-------------+ | employee_id | +-------------+ | 2 | | 77 | | 4 | | 7 | +-------------+ Explanation: The head of the company is the employee with employee_id 1. The employees with employee_id 2 and 77 report their work directly to the head of the company. The employee with employee_id 4 reports their work indirectly to the head of the company 4 --> 2 --> 1. The employee with employee_id 7 reports their work indirectly to the head of the company 7 --> 4 --> 2 --> 1. The employees with employee_id 3, 8, and 9 do not report their work to the head of the company directly or indirectly.
Problem Overview: The table Employees stores an organizational hierarchy using employee_id and manager_id. The task is to return all employees who ultimately report to a specific manager (manager id = 1) through the reporting chain.
Approach 1: Two Joins (Self Join Hierarchy Traversal) (Time: O(n), Space: O(1))
This approach walks the reporting hierarchy using SQL self joins. Each row in Employees links an employee to their direct manager via manager_id. By joining the table to itself twice, you can follow the chain employee → manager → given manager. The first join connects an employee to their manager, and the second join connects that manager to the top-level manager (id = 1).
The key insight is that organizational hierarchies are naturally represented as parent-child relationships. A self join lets you traverse those relationships directly inside SQL without recursion. Once the join chain confirms the top manager, filter out the manager record itself and return the employee ids.
This pattern is common when querying hierarchical data in relational databases. It appears frequently in interview-style SQL problems involving reporting structures, org charts, or category trees. Understanding how to chain self joins is essential when working with database problems that store relationships in a single table.
When the hierarchy depth is known or limited, a fixed number of joins is efficient and easy to read. For deeper or unknown hierarchies, recursive queries or CTEs are usually preferred. However, in this problem a simple self-join chain solves it cleanly using standard SQL operations and joins.
Recommended for interviews: The two self-join approach is the expected solution. It demonstrates that you understand hierarchical relationships in relational tables and can traverse them using joins. Simpler brute-force approaches or repeated queries show the idea, but the self-join solution proves solid SQL fundamentals.
We can use two joins to find all employees who report directly or indirectly to the company CEO.
Specifically, we first use a join to find the manager_id of the superior manager for each manager_id, and then use another join to find the manager_id of the higher-level manager. Finally, if the manager_id of the higher-level manager is 1 and the employee_id of the employee is not 1, it means that the employee reports directly or indirectly to the company CEO.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Two Self Joins (Hierarchy Traversal) | O(n) | O(1) | Best when hierarchy depth is known and small. Efficient for manager → employee relationship queries. |
| Subquery with IN | O(n) | O(1) | Readable alternative when filtering employees whose managers match a specific condition. |
LeetCode Medium 1270 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 6,138 views views
Watch 6 more video solutions →Practice All People Report to the Given Manager with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor