Hierarchical queries in Oracle

Question

I have a table with a structure like (id, parent_id) in Oracle11g.

id    parent_id
---------------
1     (null)
2     (null)
3     1
4     3
5     3
6     2
7     2

I'd like to query it to get all the lines that are hierarchically linked to each of these id, so the results should be :

root_id  id    parent_id
------------------------
1        3     1
1        4     3
1        5     3
2        6     2
2        7     2
3        4     3
3        5     3

I've been struggling with the connect by and start with for quite some time now, and all i can get is a fraction of the results i want with queries like :

select connect_by_root(id) root_id, id, parent_id from my-table
start with id=1
connect by prior id = parent_id

I'd like to not use any for loop to get my complete results.

Any Idea ?

Best regards, Jérôme Lefrère

PS : edited after first answer, noticing me i had forgotten some of the results i want...

Allan · Accepted Answer · 2012-05-22 16:23:00Z

3

The query you posted is missing the from clause and left an underscore out of connect_by_root, but I'll assume those aren't actually the source of your problem.

The following query gives you the result you're looking for:

select * from (
   select connect_by_root(id) root_id, id, parent_id
   from test1
   start with parent_id is null
   connect by prior id = parent_id)
where root_id <> id

The central problem is that you were specifying a specific value to start from, rather that specifying a way to identify the root rows. Changing id = 1 to parent_id is null allows the entire contents of the table to be returned.

I also added the outer query to filter the root rows out of the result set, which wasn't mentioned in your question, but was shown in your desired result.

SQL Fiddle Example

Comment Response:

In the version provided, you do get descendants of id = 3, but not in such a way that 3 is the root. This is because we're starting at the absolute root. Resolving this is easy, just omit the start with clause:

SELECT *
FROM
  (SELECT connect_by_root(id) root_id,
          id,
          parent_id
   FROM test1
CONNECT BY
   PRIOR id = parent_id)
WHERE root_id <> id

SQL Fiddle Example

edited May 22, 2012 at 16:23

answered May 22, 2012 at 15:01

Allan

17.5k5 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jerome Over a year ago

Thanks for your answer. I edited my post so the from and missing underscore are not a problem anymore. Your query gets me the absolute roots. I'd like to get also the descendants of the id=3 line

A.B.Cade · Accepted Answer · 2012-05-22 15:00:48Z

2

try this:

select connect_by_root(id) root_id, id, parent_id
from your_table
start with parent_id is null
connect by prior id = parent_id

answered May 22, 2012 at 15:00

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

3 Comments

Jerome Over a year ago

Thanks for your answer which was perfect until I noticed that I forgot some of the results i want : i want also the lines that are not absolute roots (see the last 2 results of my original post)

A.B.Cade Over a year ago

then just remove the start with parent_id is null part

Jerome Over a year ago

It seems to work. Just need to check this on my more complex table to be sure

ilja · Accepted Answer · 2019-05-20 14:22:16Z

0

It will give you the exact result you want:

select connect_by_root(id) as root, id, parent_id
from test1
connect by prior id=parent_id
start with parent_id is not null;

edited May 20, 2019 at 14:22

ilja

2,7022 gold badges18 silver badges23 bronze badges

answered May 20, 2019 at 12:54

Pranav Sri

913 bronze badges

Collectives™ on Stack Overflow

Hierarchical queries in Oracle

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related