CODEWITHSUNDEEP
sqloracle-database
Stefania
Stefania

Reputation: 683

hierarchical query on Oracle

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:

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.

Upvotes: 0

Views: 108

Answers (1)

sampathsris
sampathsris

Reputation: 22290

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?

Upvotes: 3

Related Questions