Self Referencing Table SQL query

Question

I've table with four columns id, name, designation, manager_id.

Table schema:

CREATE TABLE "Employee_Information" 
(
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
    "name" varchar, 
    "designation" varchar, 
    "manager_id" integer references employee_information(id)
);

It is as follows

ID  Name    Designation   Manager_id
-------------------------------------
1   Raja    CEO 
2   Mani    CTO           1
3   Kavi    COO           1
4   Murugan Head          3
5   Alpha   Head(Fin)     4
7   Kannan  Head          4

Employee hierarchy is as follows:

Raja CEO
    Mani CTO
    Kavi COO
               Murugan Head
                       Alpha Head(Fin)
                           Kannan Head 
       Beta CFO
       Delta Head 

I want an SQL query to display all possible manager for particular employee. His Juniors or other sub level employees name are not supposed to be in the results set.

Display all others employees on same level or above.

I'm unable to figure out a solution for this.

Answer 1

Ref: SQLite WITH clause

You need a "Recursive CTE" (common table expression) to traverse the organization hierarchy. Like this:

Query

WITH RECURSIVE Emp_CTE (ID, Name, Designation, Manager_id, Manager_name)
AS (
    SELECT ID, Name, Designation, Manager_id, cast(NULL as varchar)
    FROM Employee_Information
    WHERE Manager_ID IS NULL
    UNION ALL
        SELECT e.ID, e.Name, e.Designation, e.Manager_id, Emp_CTE.Name
        FROM Employee_Information e
        INNER JOIN Emp_CTE ON Emp_CTE.ID = e.Manager_id
    )
SELECT *
FROM Emp_CTE

Result:

| ID |  Name   | Designation | Manager_id | Manager_name |
|----|---------|-------------|------------|--------------|
|  1 | Raja    | CEO         | null       | null         |
|  3 | Kavi    | COO         | 1          | Raja         |
|  2 | Mani    | CTO         | 1          | Raja         |
|  4 | Murugan | Head        | 3          | Kavi         |
|  5 | Alpha   | Head(Fin)   | 4          | Murugan      |
|  7 | Kannan  | Head        | 4          | Murugan      |

Setup:

CREATE TABLE "Employee_Information" ("id" INTEGER PRIMARY KEY AUTOINCREMENT 
NOT NULL, "name" varchar, "designation" varchar, "manager_id" integer references employee_information(id));



INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (1, 'Raja', 'CEO', NULL)
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (2, 'Mani', 'CTO', '1')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (3, 'Kavi', 'COO', '1')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (4, 'Murugan', 'Head', '3')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (5, 'Alpha', 'Head(Fin)', '4')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (7, 'Kannan', 'Head', '4')
;

Demo

Query 2

WITH RECURSIVE Emp_CTE (ID, Name, Designation, Manager_id, Manager_name, namepath)
AS (
    SELECT ID, Name, Designation, Manager_id, cast(NULL as varchar), name as namepath
    FROM Employee_Information
    WHERE Manager_ID IS NULL
    UNION ALL
        SELECT e.ID, e.Name, e.Designation, e.Manager_id, Emp_CTE.Name
  , Emp_CTE.namepath || '/' || e.Name 
        FROM Employee_Information e
        INNER JOIN Emp_CTE ON Emp_CTE.ID = e.Manager_id
    )
SELECT *
FROM Emp_CTE

Result:

| ID |  Name   | Designation | Manager_id | Manager_name |         namepath         |
|----|---------|-------------|------------|--------------|--------------------------|
|  1 | Raja    | CEO         | null       | null         | Raja                     |
|  3 | Kavi    | COO         | 1          | Raja         | Raja/Kavi                |
|  2 | Mani    | CTO         | 1          | Raja         | Raja/Mani                |
|  4 | Murugan | Head        | 3          | Kavi         | Raja/Kavi/Murugan        |
|  5 | Alpha   | Head(Fin)   | 4          | Murugan      | Raja/Kavi/Murugan/Alpha  |
|  7 | Kannan  | Head        | 4          | Murugan      | Raja/Kavi/Murugan/Kannan |