oracle connect by multiple parents

Question

I am facing an issue using connect by.

I have a query through which I retrieve a few columns including these three:

• ID
• ParentID
• ObjectID

Now for the same ID and parentID, there are multiple objects associated e.g.

ID ParentID ObjectID
1    0             112
1    0             113
2    0             111
2    0             112
3    1             111
4    1             112

I am trying to use connect by but I'm unable to get the result in a proper hierarchy. I need it the way it is showed below. Take an ID-parentID combo, display all rows with that ID-parentID and then all the children of this ID i.e. whose parentID=ID

ID ParentID ObjectID
1    0             112
1    0             113
3    1             111
4    1             112
2    0             111
2    0             112

select ID,parent_id, object_id from table start with parent_id=0 
connect by prior id=parent_id order by id,parent_id

Above query is not resulting into proper hierarchy that i need.

Answer 1

First of all Thanks to all who tried helping me.

Finally i changed my approach as applying hierarchy CONNECT BY clause to inner queryw ith multiple joins was not working for me.

I took following approach

1. Get the hierarchical data from First table i.e. table with ID-ParentID. Select Query table1 using CONNECT BY. It will give the ID in proper sequence. Join the retrieved List of ID.

2. Pass the above ID as comma seperated string in select query IN Clause to second table with ID-ObjectID.

select * from table2 where ID in (above Joined string of ID) order by instr('above Joined string of ID',ID);

ORDER BY INSTR did the magic. It will give me the result ordered by the IN Clause data and IN Clause string is prepared using the hierarchical query. Hence it will obviously be in sequence.

Again Thanks all for the help!

Note: Above approach has one constraint : ID passed as comma separated string in IN Clause. IN Clause has a limit of characters inside it. I guess 1000 chars. Not sure. But as i am sure on the data of First table that it will not be so much so as to cross limit of 1000 chars. Hence i chose above approach.

Answer 2

Well, your problem appears to be that you are using a non-normalized table design. If a given ID always has the same ParentID, that relationship shouldn't be indicated separately in all these rows.

A better design would be to have a single table showing the parent child relationships, with ID as a primary key, and a second table showing the mappings of ID to ObjectID, where I presume both columns together would comprise the primary key. Then you would apply your hierarchical query against the first table, and join the results of that to the other table to get the relevant objects for each row.

You can emulate this with your current table structure ...

with parent_child as (select distinct id, parent_id from table),
     tree as (select id, parent_id from parent_child
               start with parent_id = 0
               connect by prior id = parent_id )
select id, table.parent_id, table.object_id
  from tree join table using (id)

Answer 3

Here's a script that runs. Not ideal but will work -

select * from (select distinct test.id,
                    parent_id,
                    object_id,
                    connect_by_root test.id root
      from test
     start with test.parent_id = 0
    connect by prior test.id = parent_id)
order by root,id