Question

Generate a list of employees (last name and first name) and their supervisors (last name and first name of supervisor aliased to ReportsToLastName and ReportsToFirstName).

(Hint: When joining a table to itself, you must use table aliases.)

-- Show your answers both in self-join as well as recursive CTE. CREATE a self-join and recursive CTE for the above query.

Answer 1

--Creating an example employee table :

CREATE TABLE employees

(

  employee_id int NOT NULL PRIMARY KEY,

  first_name varchar(50) NOT NULL,

  last_name varchar(50) NOT NULL,

  supervisor_id int NULL

);

--Insert some random example data

INSERT INTO employees VALUES (1, 'JJ', 'thyns', NULL);

INSERT INTO employees VALUES (2, 'Tem', 'Rob', 1);

INSERT INTO employees VALUES (3, 'Dino', 'Durel', 1);

INSERT INTO employees VALUES (4, 'Rob', 'Peter', 2);

INSERT INTO employees VALUES (5, 'Kent', 'Fryn', 2);

INSERT INTO employees VALUES (6, 'Bill', 'Gates', 3);

INSERT INTO employees VALUES (7, 'John', 'Miller', 3);

INSERT INTO employees VALUES (8, 'Shyan', 'Markester', 5);

INSERT INTO employees VALUES (9, 'Jacob', 'Matthew', 6);

INSERT INTO employees VALUES (10, 'Michael', 'Jackson', 6) ;

Query using ‘self join’ (Without adding the top employee who has no supervisor) :

SELECT e.last_name as LastName, e.first_name as FirstName,

               s.last_name as ReportsToLastName, s.first_name as ReportsToFirstName

FROM employees e

JOIN employees s ON s .employee_id = e.supervisor_id ;

Query using ‘self join’ (With the topmost supervisor ) :

SELECT e.last_name as LastName, e.first_name as FirstName,

               s.last_name as ReportsToLastName, s.first_name as ReportsToFirstName

FROM employees e

LEFT JOIN employees s ON s .employee_id = e.supervisor_id ;

Query using ‘With’ clause to show recursive CTE : (Note: This query is written in PostgreSQL, if using SQL server replace “WITH RECURSIVE” with “WITH” )

WITH RECURSIVE cte (emp_id,fname, lname, supervisor_id, emplevel)

AS

  (

    SELECT employee_id, first_name, last_name, supervisor_id, 1

    FROM employees

    WHERE supervisor_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.first_name, e.last_name, e.supervisor_id,

      r.emplevel + 1

    FROM employees e

      INNER JOIN cte r

        ON e.supervisor_id = r.emp_id

  )

SELECT lname as LastName, fname as FirstName,

  (SELECT last_name

   FROM employees

    WHERE employee_id = cte.supervisor_id) AS ReportsToLastName ,

(SELECT first_name

   FROM employees

    WHERE employee_id = cte.supervisor_id) AS ReportsToFirstName

FROM cte ;

Output screenshots: