All People Report to the Given Manager - Problem

You are given an Employees table representing the company hierarchy. Each employee has an ID, name, and their direct manager's ID.

Write a SQL solution to find all employees that directly or indirectly report to the head of the company (employee_id = 1).

Key Points:

  • Direct reporting: Employee → Manager
  • Indirect reporting: Employee → Manager → Manager's Manager (up to 3 levels)
  • The head of company has employee_id = 1
  • Maximum hierarchy depth is 3 managers

Table Schema

Employees
Column Name Type Description
employee_id PK int Unique employee identifier
employee_name varchar Employee's full name
manager_id int ID of direct manager (NULL for head)
Primary Key: employee_id
Note: employee_id = 1 is the head of company. manager_id can be NULL for the head.

Input & Output

Example 1 — Complete Hierarchy
Input Table:
employee_id employee_name manager_id
1 Boss
2 Alice 1
3 Bob 2
4 Charlie 2
5 David 3
Output:
employee_id
2
3
4
5
💡 Note:

Boss (ID=1) is the head. Alice (ID=2) reports directly to Boss. Bob and Charlie (ID=3,4) report to Alice, so indirectly to Boss. David (ID=5) reports to Bob, so indirectly to Boss through 2 levels.
Example 2 — Only Direct Reports
Input Table:
employee_id employee_name manager_id
1 Boss
2 Alice 1
3 Bob 1
Output:
employee_id
2
3
💡 Note:

Simple case where Alice and Bob both report directly to the head (Boss). No indirect reporting levels exist.
Example 3 — Separate Branch
Input Table:
employee_id employee_name manager_id
1 Boss
2 Alice 1
7 Eve 6
6 Frank
Output:
employee_id
2
💡 Note:

Only Alice reports to Boss (ID=1). Eve reports to Frank (ID=6) who is a separate head, so Eve is not included in the results.

Constraints

  • 1 ≤ employee_id ≤ 2000
  • employee_id is unique for each employee
  • manager_id is NULL only for company heads
  • Hierarchy depth will not exceed 3 levels below head

Visualization

Tap to expand
All People Report to the Given Manager INPUT Employees Table emp_id name manager_id 1 Boss NULL 3 Alice 3 2 Bob 1 4 Dan 2 7 Eve 4 8 Frank 3 Hierarchy (3 levels) Boss (1) Bob (2) Dan (4) Eve (7) ALGORITHM STEPS 1 Self-Join Level 1 Find direct reports to Boss e1.manager_id = 1 --> Bob(2) 2 Self-Join Level 2 Join to find 2nd level reports e2.manager_id = e1.id --> Dan(4) 3 Self-Join Level 3 Join to find 3rd level reports e3.manager_id = e2.id --> Eve(7) 4 Filter and Combine Exclude Boss, get unique IDs WHERE e3.employee_id != 1 SELECT DISTINCT e3.employee_id FROM Employees e1 JOIN e2 JOIN e3 (3 self-joins) FINAL RESULT Employees Reporting to Boss Boss (1) excluded Bob (2) Level 1 OK Dan (4) Level 2 OK Eve (7) Level 3 OK Output: employee_id employee_id 2 4 7 3 employees found Key Insight: Use 3 self-joins on Employees table to traverse the management hierarchy up to 3 levels deep. Each join connects employee to their manager: e1 (level 1) --> e2 (level 2) --> e3 (level 3). Filter WHERE e3.employee_id != 1 excludes the head manager from results, keeping only subordinates. TutorialsPoint - All People Report to the Given Manager | Optimal Solution (3 Self-Joins)

Related Problems

Asked in
Facebook 28 Amazon 22 Microsoft 15
28.5K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen