CODEWITHSUNDEEP
sqloracle-databasehierarchical
mesopotamia
mesopotamia

Reputation: 393

Hierarchical query from two different tables in oracle sql

I have a table that contains data something like this

TABLE_A

ID      PARENT_ID     NAME     PROJECT_ID
1                     abc      
2       1             def      
3       2             ghi
4       3             jkl      101
5       1             mno

and I have another table that contains some data that depends on first table 'project_id' : 

TABLE_B
ID      PROJECT_ID    NAME
1       101           prs
2       101           tuv
3       102           xyz      
4       102           hgf

I want a result something like this ;

abc
def
ghi
jkl
prs
tuv
mno

I have tried something like this but I did not know how to connect 'TABLE_B'

SELECT LEVEL, A.NAME
 FROM TABLE_A A
 CONNECT BY PRIOR A.ID = PRIOR A.PARENT_ID
 ORDER BY LEVEL;

Upvotes: 1

Views: 1094

Answers (1)

Aleksej
Aleksej

Reputation: 22979

If I understand well your need, this could be a way:

/* building a test case */
with TABLE_A(ID, PARENT_ID, NAME, PROJECT_ID) as (
    select 1,       null,          'abc',      null from dual union all
    select 2,       1   ,          'def',      null from dual union all
    select 3,       2   ,          'ghi',      null from dual union all
    select 4,       3   ,          'jkl',      101  from dual union all
    select 5,       1   ,          'mno',      null from dual
),TABLE_B(ID, PROJECT_ID, NAME) as (
    select 1,       101,           'prs' from dual union all
    select 2,       101,           'tuv' from dual union all
    select 3,       102,           'xyz' from dual union all
    select 4,       102,           'hgf' from dual
)
/* the query */
select name
from (
        select ID, PARENT_ID, NAME, PROJECT_ID
        from table_a
        UNION ALL
        select a.ID, a.PARENT_ID, b.NAME, a.PROJECT_ID
        from table_b b
              inner join table_a a
                on a.project_id = b.project_id
     )
start with parent_id is null
connect by prior id = parent_id

The idea here is to build a partial result that contains all the data from table_a and table_b and then use this result in a hierarchical query as if it was a single table.

The result:

abc
def
ghi
jkl
prs
tuv
mno

Upvotes: 2

Related Questions