LeetCode: All People Report to the Given Manager Posted on August 5, 2019July 26, 2020 by braindenny All People Report to the Given Manager Similar Problems: CheatSheet: SQL & MySql CheatSheet: Leetcode For Code Interview CheatSheet: Common Code Problems & Follow-ups Tag: #sql Table: Employees +---------------+---------+ | Column Name | Type | +---------------+---------+ | employee_id | int | | employee_name | varchar | | manager_id | int | +---------------+---------+ employee_id is the primary key 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 an SQL query 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 3 managers as the company is small. Return result table in any order without duplicates. The query result format is in the following example: 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 | +-------------+---------------+------------+ Result table: +-------------+ | employee_id | +-------------+ | 2 | | 77 | | 4 | | 7 | +-------------+ 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 report his work indirectly to the head of the company 4 --> 2 --> 1. The employee with employee_id 7 report his work indirectly to the head of the company 7 --> 4 --> 2 --> 1. The employees with employee_id 3, 8 and 9 don't report their work to head of company directly or indirectly. Github: code.dennyzhang.com Credits To: leetcode.com Leave me comments, if you have better ways to solve. Solution: inner join ## https://code.dennyzhang.com/all-people-report-to-the-given-manager ## t3: directly report to employee_id 1 ## t2: directly report to t3 ## t1: directly report to t2 select t1.employee_id from Employees as t1 inner join Employees as t2 on t1.manager_id = t2.employee_id join Employees as t3 on t2.manager_id = t3.employee_id where t3.manager_id = 1 and t1.employee_id != 1 Solution: subquery ## https://code.dennyzhang.com/all-people-report-to-the-given-manager select distinct employee_id from ( select employee_id from Employees where manager_id in (select employee_id from Employees where manager_id in (select employee_id from Employees where manager_id = 1)) union select employee_id from Employees where manager_id in (select employee_id from Employees where manager_id = 1) union select employee_id from Employees where manager_id = 1) as t where employee_id != 1 Post Views: 0 Post navigation LeetCode: HexspeakLeetCode: String Transforms Into Another String Leave a Reply Cancel replyYour email address will not be published.Comment Name Email Website