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.
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')
;
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 |