Description

Table: Employees

Column NameType
employee_idint
employee_namevarchar
manager_idint
  • 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.

Problem Statement

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_idemployee_namemanager_id
1Boss1
3Alice3
2Bob1
4Daniel2
7Luis4
8Jhon3
9Angela8
77Robert1

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.

Solution

This problem can be broken down into three parts.

  1. Find the head of the company.
  2. Find employees reporting to the head of the company.
  3. Find employees reporting to the employees reporting to the head of the company. This is recursive relationship.

Finding the head of the company

For finding the head of the company, you can use where clause as it’s clearly mentioned that employee with employee_id 1 is the head of the company.

Find employees reporting to the head

First we need to find the users whose manager is manager_id=1. Notice that this will also return employee_id=1. We do not want to return the head of the company in the output. So, we need to exclude this employee using employee_id!=1 from the output.

1SELECT employee_id, manager_id
2    FROM Employees
3    WHERE manager_id = 1 AND employee_id != 1

Find employees reporting to the employees reporting to the head

This is where we need recursive CTE. We can union the results with itself to get all employees reporting to the head of the company. This will return the full result of the problem.

 1WITH RECURSIVE cte AS (
 2    SELECT employee_id, manager_id
 3    FROM Employees
 4    WHERE manager_id = 1 AND employee_id != 1
 5    UNION ALL
 6    SELECT e.employee_id, e.manager_id
 7    FROM Employees e
 8    JOIN cte ON e.manager_id = cte.employee_id
 9)
10SELECT employee_id
11FROM cte;