Oracle Hierarchical Query spanning across multiple tables

Question

In the current project where I am working, I have come across a peculiar situation. Please consider the following table structure:

AGY_AGENCY (
  AGENCY_ID       NUMBER(9) not null,
  AGENCY_CD       VARCHAR2(30) not null,
  AGC_LEG_CD      VARCHAR2(30) not null,
  ........................
)

AGY_RELN (
  AGY_RELN_ID            NUMBER(9) not null,
  AGENCY_ID              NUMBER(9) not null,   -- Refers to AGY_AGENCY.AGENCY_ID
  RELATIONSHIP_LINK_TYPE VARCHAR2(30) not null,
  ............................
)

AGY_REL_AGENCY (
  REL_AGY_ID        NUMBER(9) not null,
  AGY_RELN_ID       NUMBER(9) not null, -- Refers to AGY_RELN.AGY_RELN_ID
  RELN_AGENCY_ID    NUMBER(9) not null, -- Refers to AGY_AGENCY.ACY_AGENCY_ID
  ...............................
)

Below is a sample data

AGY_AGENCY 

AGENCY_ID  AGENCY_CD  AGC_LEG_CD
--------------------------
1000,      'ABC',    'ABC'
1001,      'DEF',    'DEF'

AGY_RELN 

AGY_RELN_ID  AGENCY_ID   RELATIONSHIP_LINK_TYPE
----------------------------------------------- 
2000,        1000,       'PARENT_OUTLET'

AGY_REL_AGENCY 

REL_AGY_ID   AGY_RELN_ID    RELN_AGENCY_ID
--------------------------------------------
3000,        2000,          1001

As per this data, Agency "DEF" is a parent outlet agency of "ABC".

I need to formulate a sql query that will return all the parents, grandparents. AGENCY_IDs (the data can span across multiple levels of hierarchy) starting from a particular AGENCY_ID.

Answer 1

EDIT: Sorry, thought ABC is parent of DEF, but checked again and saw you say, DEF is parent of ABC, so i changed my answer accordingly

This table structure is a bit strange, two tables would be quite enough. But never mind about it.

Hierarchical queries are executed on tables that contains reference to itself. So, what you need here is to join these tables to get a single result set and execute connect by hierarchical query on it.

Considering you are looking for a specific record's parents. Say that is agency_cd = 'ABC' this is how you are going to get it.

select agency_id, agency_cd, level from (
 --Below query will join three tables to get a record and its parent id side-by-side in a row    
 SELECT ag.*, rlag.reln_agency_id AS parent_agency_id 
        FROM agy_agency ag, agy_reln rl, agy_rel_agency rlag  
         WHERE ag.agency_id = rl.agency_id (+)
           AND  rlag.agy_reln_id(+) = rl.agy_reln_id    
) t
where agency_cd <> 'ABC' --discard the record you are looking for itself
connect by agency_id =  prior parent_agency_id -- Connect by is executed before where clause, don't worry about where clause
 start with agency_cd = 'ABC';

On the other hand. If you would like to see the whole table hieararchically. Try below query.

 select agency_id, agency_cd, prior agency_id as parent_agency_id, prior agency_cd      as parent_agency_cd, level from (
     SELECT ag.*, rlag.reln_agency_id AS parent_agency_id 
       FROM agy_agency ag, agy_reln rl, agy_rel_agency rlag  
         WHERE ag.agency_id = rl.agency_id (+)
          AND  rlag.agy_reln_id(+) = rl.agy_reln_id
 ) t
 connect by prior agency_id =  parent_agency_id 
 start with parent_agency_id is null    

Here you are, the sql fiddle for your example http://www.sqlfiddle.com/#!4/3f692/5