I require assistance with an Oracle SQL query

by Yusuf

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.

Comments for I require assistance with an Oracle SQL query

Average Rating starstarstarstarstar

Click here to add your own comments

Nov 03, 2016
Rating
starstarstarstarstar
Use a hierarchical query
by: John

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

The Oracle Database SQL Language Reference has more details om hierarchical queries.

You might also want to review our Oracle SQL tutorials especially designed for beginners. Also see our Oracle training page for information about formal training courses both on-line and in person.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.