3

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.

  1. I would like to show the whole tree structure.
  2. I would like to show only the children nodes
  3. I would like to show only the parent nodes

SampleDataImage

Senior .Net Dev3loper
  • 111
  • 1
  • 1
  • 9
  • Please add some sample data, desired result and what you tried so far – Aleksej May 10 '16 at 09:23
  • 1
    http://stackoverflow.com/questions/tagged/connect-by+oracle or http://stackoverflow.com/questions/tagged/hierarchical-data+oracle or http://stackoverflow.com/questions/tagged/oracle+recursive-query –  May 10 '16 at 09:44
  • Thanks for the quick reply! I edit the post and added a sample data image. Also I would like to show the whole tree structure with an sql query and on another query I would like to show only the children nodes and on a third query I would like to show only what are parent nodes – Senior .Net Dev3loper May 10 '16 at 09:45
  • Possible duplicate of [Oracle SQL creating different levels of data from a single table](http://stackoverflow.com/questions/36085015/oracle-sql-creating-different-levels-of-data-from-a-single-table) – Frank Schmitt May 10 '16 at 09:59
  • Welcome to SO! Please take the time to search for similar questions on SO before asking a new one - there are literally *tons* of questions on SO about Oracle and hierarchical data. – Frank Schmitt May 10 '16 at 10:00

1 Answers1

17

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;
MT0
  • 143,790
  • 11
  • 59
  • 117