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

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

## SQL Challenges-1: Exercise-45 with Solution

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:

FieldTypeNullKeyDefaultExtra
employee_idint(11)NOPRI
first_namevarchar(25)YES
manager_idint(11)YES

Data:

employee_idfirst_namemanager_id
100Steven100
101Neena100
102Lex100
103Alexander102
104Bruce103
105David103
106Valli103
107Diana103
108Nancy 101
109Daniel108
110John108

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|
```

SQL Code Editor: