I would like to show a tree structure in SQL with child nodes and parent nodes. I have a table like:
Employee
-------------
ID (int)
FirstName (varchar)
LastName (varchar)
ParentID (int)
Job (varchar)
which represents an employee. ParentID represent the manager of the employee has. I would like to have this table only with this structure.
Query - The whole tree structure:
SELECT *
FROM Employee
START WITH ParentID IS NULL
CONNECT BY PRIOR ID = ParentID
ORDER SIBLINGS BY LastName, FirstName, ID;
Query - The children of a given employee:
You do not need a hierarchical query for this.
(The parent is given by the bind variable :parent_id
)
SELECT *
FROM Employee
WHERE ParentID = :parent_id
ORDER BY LastName, FirstName, ID;
Query - The descendants of a given employee:
The same query as for the whole tree but with a different start point
(The parent is given by the bind variable :parent_id
)
SELECT *
FROM Employee
START WITH ParentID = :parent_id
CONNECT BY PRIOR ID = ParentID
ORDER SIBLINGS BY LastName, FirstName, ID;
Query - The employee and their ancestors:
Similar to the previous query but with the CONNECT BY
reversed and you won't need to order the siblings as there will only be one immediate manager per employee.
(The employee is given by the bind variable :employee_id
)
SELECT *
FROM Employee
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;
Query - The employee's manager:
Identical to the previous query but with a filter LEVEL = 2
to just get the immediate parent row.
(The employee is given by the bind variable :employee_id
)
SELECT e.*
FROM Employee e
WHERE LEVEL = 2
START WITH ID = :employee_id
CONNECT BY PRIOR ParentID = ID;