hierarchical query on Oracle

Question

I'm trying to write a hierarchical query on Oracle that involves two tables:

GeoNodes
Name, uuid, lat, lon

GeoParents
parent_uuid, child_uuid

Example data:

GeoNodes
Name    UUID  lat  lon
test1   123   0    0
test2   124   0    0
test3   125   0    0
test4   126   0    0

GeoParents
parent_uuid   child_uuid
123           124
123           125
124           126

In this example, I have:

test1 parent of test2 and test3
test2 parent of test4

There is no null values in geoparents table. On geoparents there are two FK (one for child and one for parent) on uuid column of geonodes that maintains data integrity between the two tables.

As you can see the link between parent and child stay on an external table. To give more complexity to all, some nodes have more than one parent.

Sadly I don't understand where to put the join in the query to make it works properly. I've searched for examples but all of them have the parent column in the data table.

This is the only query that extracts some congruent data, but unfortunately is not able to obtain roots records:

select Lpad(soc || '_' || name,Length(soc || '_' || name) + LEVEL * 3 - 3,'-') 
from geonodes g, geoparents p
where g.uuid = p.child_uuid
start with name = 'myTest'
connect by prior g.uuid = p.PARENT_UUID
order siblings by name;

Thanks in advance for your help.

Can you post some data example please.

Jorge Campos
– Jorge Campos

2014-07-24 12:07:27 +00:00
Commented Jul 24, 2014 at 12:07 — Jorge Campos
– Jorge Campos, Commented Jul 24, 2014 at 12:07
Connect by prior should help.

Sid
– Sid

2014-07-24 12:12:38 +00:00
Commented Jul 24, 2014 at 12:12 — Sid
– Sid, Commented Jul 24, 2014 at 12:12

sampathsris · Accepted Answer · 2014-07-24 12:30:02Z

3

First, you construct the tree:

SELECT child_uuid uuid
FROM   geoparents
CONNECT BY PRIOR child_uuid = parent_uuid
START WITH parent_uuid IS NULL;

Then you can join the table with information:

SELECT *
FROM (SELECT child_uuid uuid
      FROM   geoparents
      CONNECT BY PRIOR child_uuid = parent_uuid
      START WITH parent_uuid IS NULL) tree,
      geonodes
WHERE tree.uuid = geonodes.uuid;

But I don't think you need to have two tables. What is stopping you from storing the parent_uuid in geonodes table?

edited Jul 24, 2014 at 12:30

answered Jul 24, 2014 at 12:11

sampathsris

22.4k12 gold badges74 silver badges102 bronze badges

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

6 Comments

Jorge Campos Over a year ago

parent_uuid = NULL; should be parent_uuid is null in oracle?

Stefania Over a year ago

This DB had been built using jpa, I was not involved in the projet of it and the actual structure cannot be modified, so I can't store the parent_uuid in geonodes table. The geoparents hasn't a row with parent_uuid equals to null, the root is the parent which never appears in the children column.

sampathsris Over a year ago

@StefaniaLori: Then you should START WITH child_uuid IS NULL and SELECT parent_uuid uuid instead of child_uuid.

Stefania Over a year ago

Sorry, I forget to specify that in the join table (geoparents) there isn't any null value. All the couples correspond to existing uuid of geonodes and there are two fk that maintains data integrity.

sampathsris Over a year ago

Are there multiple root nodes? If there's a single root node you can getaway by hardcoding that.

|

Collectives™ on Stack Overflow

hierarchical query on Oracle

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related