Oracle Recursive Query Connect By Loop in data

Question

I have a table that looks essentially like this (the first row pk1=1 is the parent row)

pk1	event_id	parent_event_id
1	123	123
2	456	123
3	789	456

Given any particular row in the above table, I need a query that returns all the related rows (up and down the hierarchy). I was trying to do this via an initial CTE table that grabs all the parent rows. Then use that as my base table and join back into the above table using a recursive query to navigate down (this seems wildly inefficient and I assume there is a better way???).

However, trying even the first step (populating my CTE table) and using a query like below to navigate up returns the connect by LOOP error.

    select event_id, level  
    from myTable   
    start with pk1 = 2  
    connect by prior parent_event_id = event_id

I assume this is due to the fact the parent row is self-referencing (event_id = parent_event_id)? If I add in the NOCYCLE statement, then the recursion stops at the row prior to the actual parent.

Two questions:
1.) Is there a better way to do this in one query?
2.) Any clue how to tweak the above to get the parent row returned?

Thanks

kfinity · Accepted Answer · 2021-02-10 16:30:04Z

I'm not super clear on what you mean by "all the related rows (up and down the tree)", but it might be possible.

Here, I'm adding more logic to the connect clause to go up OR down the tree. This includes direct parents and descendants, but also includes siblings/cousins to the starting node. That might or might not be what you want.

with mytable as (select 1 as pk1, 123 as event_id, 123 as parent_event_id from dual
    union select 2, 456, 123 from dual
    union select 3, 789, 456 from dual
    union select 4, 837, 123 from dual)
select pk1, event_id, level, SYS_CONNECT_BY_PATH(event_id, '/') as path
    from myTable   
    start with pk1 = 2  
    connect by nocycle (prior parent_event_id = event_id and prior event_id <> event_id)
      or (prior event_id = parent_event_id)

The tweak to get the root parent to show up is just and prior event_id <> event_id - ie, don't go further up the tree if the parent node = the current node.

I added an example row (pk1=4) to show a sibling row (not direct parent or descendant) being returned.

Collectives™ on Stack Overflow

Oracle Recursive Query Connect By Loop in data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related