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

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)