Who is your manager?

In this exercise, we are going to use the dataset of an IT-organization which is provided in the table employee. The table has the fields ID (ID number of the employee), Name (the employee's name), and Supervisor (ID number of the supervisor).

The IT-organization consists of different roles and levels.

A section of the entire hierarchy

The organization has one IT director (ID=1, Heinz Griesser, Supervisor=0) with many subordinate employees. Under the IT director you can find the IT architecture manager (ID=10, Andreas Sternig, Supervisor=1) with three subordinate employees. For Andreas Sternig Supervisor=1 which is the IDof the IT-Director.

First, we want to answer the question: Who are the supervisors for each employee?

We are going to solve this problem by recursively querying the dataset.

This exercise is part of the course

Hierarchical and Recursive Queries in SQL Server

View Course

Exercise instructions

Create a CTE with the name employee_hierarchy.
Select the information of the IT director as the initial step of the CTE by filtering on his Supervisor ID.
Perform a join with employee to get the name of the manager.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Create the CTE employee_hierarchy
WITH ___ AS (
	SELECT
		ID, 
  		NAME,
  		Supervisor
	FROM employee
  	-- Start with the IT Director
	WHERE ___ = ___
	UNION ALL
	SELECT 
  		emp.ID,
  		emp.NAME,
  		emp.Supervisor
	FROM employee emp
  		JOIN employee_hierarchy
  		ON emp.Supervisor = employee_hierarchy.ID)
    
SELECT 
    cte.Name as EmployeeName,
    emp.Name as ManagerName
FROM employee_hierarchy as cte
	JOIN employee as emp
	-- Perform the JOIN on Supervisor and ID
	ON cte.___ = emp.___;

Edit and Run Code