I require assistance with an SQL query

by Yusuf

Question:

Record name is TREE. The two fields are PARENT & CHILD.

It's a tree structure i.e.
PARENT = 0001
CHILD = 2000
CHILD = 3000
PARENT = 2000
CHILD = 2500

I require an Oracle SQL statement stating that parent1 = 0001 and children = 2000 & 3000. Another level indicates - parent2 2000 & child = 2500.

I hope you help me with the SQL query.

Answer:

You want a hierarchical query - something like this:

SELECT parent, child FROM tree CONNECT BY PRIOR tree_id=parent_id

The records will be returned depth first.

It's easier to explain using the example of the employees table in the sample hr schema that is installed with the Oracle database. This table has employee_id
as the primary key (the unique identifier) of the employee table and manager_id as the link to the manager's employee record.

SELECT employee_id FROM employees CONNECT BY PRIOR employee_id=manager_id


That produces something like the following (this is just a partial output to show how it works) :

EMPLOYEE_ID MANAGER_ID
----------- ----------
101 100
108 101
109 108
110 108
111 108
200 101