SQL Challenges-1: All People Report to the Given Manager

Last update on October 15 2025 12:47:28 (UTC/GMT +8 hours)

45. All People Report to the Given Manager

From the following table write a SQL query to find all employees that directly or indirectly report to the head of the company. Return employee_id, name, and manager_id.

Input:

Table: emp_test_table

Structure:

Field	Type	Null	Key
employee_id	int(11)	NO	PRI
first_name	varchar(25)	YES
manager_id	int(11)	YES

Data:

employee_id	first_name	manager_id
100	Steven	100
101	Neena	100
102	Lex	100
103	Alexander	102
104	Bruce	103
105	David	103
106	Valli	103
107	Diana	103
108	Nancy	101
109	Daniel	108
110	John	108

Sample Solution:

SQL Code(MySQL):

CREATE TABLE emp_test_table (
employee_id integer NOT NULL UNIQUE,
first_name varchar(25),
manager_id integer);


insert into emp_test_table values(100,'Steven     ',100); 
insert into emp_test_table values(101,'Neena      ',100); 
insert into emp_test_table values(102,'Lex        ',100); 
insert into emp_test_table values(103,'Alexander  ',102); 
insert into emp_test_table values(104,'Bruce      ',103); 
insert into emp_test_table values(105,'David      ',103); 
insert into emp_test_table values(106,'Valli      ',103); 
insert into emp_test_table values(107,'Diana      ',103); 
insert into emp_test_table values(108,'Nancy      ',101); 
insert into emp_test_table values(109,'Daniel     ',108); 
insert into emp_test_table values(110,'John       ',108); 



SELECT employee_id,first_name as Name, manager_id
FROM emp_test_table
WHERE manager_id in (SELECT employee_id from emp_test_table
WHERE manager_id in (SELECT employee_id from emp_test_table WHERE manager_id = 100))
AND employee_id != 100;

Sample Output:

employee_id|Name       |manager_id|
-----------|-----------|----------|
        101|Neena      |       100|
        102|Lex        |       100|
        103|Alexander  |       102|
        104|Bruce      |       103|
        105|David      |       103|
        106|Valli      |       103|
        107|Diana      |       103|
        108|Nancy      |       101|
        109|Daniel     |       108|
        110|John       |       108|

Go to:

PREV : Average Selling Price.
NEXT : Students and Examinations.

SQL Code Editor:

Contribute your code and comments through Disqus.