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:

Data:

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:

Contribute your code and comments through Disqus.

Previous: Average Selling Price.
Next: Students and Examinations.