[解決済み] 自己参照テーブルのSQLクエリ

質問内容

4つのカラムを持つテーブルがあります。 id, name, designation, manager_id.

テーブルのスキーマです。

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

次のようになります。

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

社員の階層は以下の通りです。

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

特定の従業員について、可能な限りすべてのマネージャを表示するSQLクエリを作成したいのです。彼の後輩や他のサブレベルの従業員の名前は、結果セットに含まれないことになっています。

同じレベルまたはそれ以上の他のすべての従業員を表示します。

これに対する解決策が見つからない。

どのように解決するのですか？

参考 SQLiteのWITH句

組織階層をたどるには、quot;Recursive CTE" (共通テーブル式)が必要です。このように。

クエリ

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

結果

| 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      |

セットアップを行います。

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

デモ

クエリ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

結果

| 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 |