3 Level SQL: GRAND PARENT - PARENT - CHILD

select
distinct root_node_id
from
(
select
g1.subject_id root_node_id
-- g1.object_id ,
-- g2.subject_id ,
-- g2.object_id ,
-- g3.subject_id ,
-- g3.object_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g1.subject_id = :subject_id
and g2.subject_id != :subject_id
and g3.subject_id != :subject_id)
union
select
g1.subject_id root_node_id
-- g1.object_id ,
-- g2.subject_id ,
-- g2.object_id ,
-- g3.subject_id ,
-- g3.object_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g2.subject_id = :subject_id
and g1.subject_id != :subject_id
and g3.subject_id != :subject_id)
union
select
g1.subject_id root_node_id
-- g1.object_id  ,
-- g2.subject_id ,
-- g2.object_id  ,
-- g3.subject_id ,
-- g3.object_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g3.subject_id = :subject_id
and g1.subject_id != :subject_id
and g2.subject_id != :subject_id)
);


--GRAND PARENT: 179695
--PARENT: 172307
--CHILD: 9912

select 
level_info, root_node_id
,(select party_name from hz_parties where party_id = root_node_id ) root_node_name
,REGEXP_SUBSTR(path_data,'[^//]+', 1, 1) level1
,REGEXP_SUBSTR(path_data,'[^//]+', 1, 2) level2
,REGEXP_SUBSTR(path_data,'[^//]+', 1, 3) level3 from (
select level level_info, self_id, child_id, self_name, child_name ,
SYS_CONNECT_BY_PATH(child_name, '//') path_data, rootnode.root_node_id
from
(select parent_id self_id, child_id, parent_name self_name, child_name from
(select HREL.SUBJECT_ID parent_id,
HREL.OBJECT_ID child_id,
HSUB.PARTY_NAME parent_name,
HOBJ.PARTY_NAME child_name
from hz_relationships HREL,
HZ_PARTIES HSUB,
HZ_PARTIES HOBJ
where 1=1
AND HREL.OBJECT_TYPE ='ORGANIZATION'
AND HREL.SUBJECT_TYPE ='ORGANIZATION'
AND HREL.RELATIONSHIP_CODE ='PARENT_OF'
AND HREL.SUBJECT_ID = HSUB.PARTY_ID
AND HREL.OBJECT_ID = HOBJ.PARTY_ID
) y
where 1=1) x,
(select
distinct root_node_id
from
(
select
g1.subject_id root_node_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g1.subject_id = :subject_id
and g2.subject_id != :subject_id
and g3.subject_id != :subject_id)
union
select
g1.subject_id root_node_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g2.subject_id = :subject_id
and g1.subject_id != :subject_id
and g3.subject_id != :subject_id)
union
select
g1.subject_id root_node_id
from
hz_relationships g1 ,
hz_relationships g2 ,
hz_relationships g3
where
1=1
and g1.object_id = g2.subject_id
and g2.object_id = g3.subject_id
AND g1.OBJECT_TYPE ='ORGANIZATION'
AND g1.SUBJECT_TYPE ='ORGANIZATION'
AND g1.RELATIONSHIP_CODE ='PARENT_OF'
AND g2.OBJECT_TYPE ='ORGANIZATION'
AND g2.SUBJECT_TYPE ='ORGANIZATION'
AND g2.RELATIONSHIP_CODE ='PARENT_OF'
AND g3.OBJECT_TYPE ='ORGANIZATION'
AND g3.SUBJECT_TYPE ='ORGANIZATION'
and (g3.subject_id = :subject_id
and g1.subject_id != :subject_id
and g2.subject_id != :subject_id)
)
) rootnode
start with x.self_id  = rootnode.root_node_id
connect by nocycle prior x.child_id = x.self_id)
order by 1,2,3,4;