Oracle Hierarchical Queries

Question

I am struggling with a query with the below requirement:

Table A

ID Name Key

1  A1   Key1
2  A2   Key2
3  A3   Key3

Table B

ID A_ID NAME CONTAINER_A_ID
1  1    B1   NULL
2  1    B2   NULL
3  1    B3   2
4  2    B4   NULL
5  2    B5   NULL
6  3    B6   NULL
7  3    B7   NULL

The Key column in table A is unique

The A_ID column in table B is a foreign key of table A

The CONTAINER_A_ID column in table B means the row in table B can be a container, it contains other data rows indicated by the CONTAINER_A_ID value.

Below is the example:

the input parameter is table A key column value, let's say A.Key = 'key1', and the result based on the above sample data will be:

A.ID  A.NAME   A.KEY  B.ID  B.A_ID  B.NAME  B.CONTAINER_A_ID
1       A1     KEY1   1     1       B1      NULL
1       A1     KEY1   2     1       B2      NULL
1       A1     KEY1   3     1       B3      2
2       A2     KEY2   4     2       B4      NULL
2       A2     KEY2   5     2       B5      NULL

if the input parameter is A.Key = 'key2', then the result will be:

A.ID  A.NAME   A.KEY  B.ID  B.A_ID  B.NAME  B.CONTAINER_A_ID
2       A2     KEY2   4     2       B4      NULL
2       A2     KEY2   5     2       B5      NULL

Thanks

Answer 1

CTEs are fine to use in 11g Oracle. I just saw Jorge is in before me. It is easier to see how the recursion works if you just use tableB in the CTE and then join to the CTE to get all the fields, like this

with 
recurse (a_id, b_id, parent_id)
as 
  (select a_id, id, container_a_id as parent_id
  from tableB 
  WHERE A_ID = 1 -- Put your parameter here
  union all
  select b.a_id, b.id, b.container_a_id
  from recurse r, tableB b
  where b.a_id = r.parent_id
  )
select r.a_id, a.name, a.key, b.id, b.a_id, b.name, b.container_a_id
from recurse r, tableA a, tableB b
where r.a_id = a.id and r.b_id = b.id
; 

This gets the same results, but although you have to use a_id and not a_key for the condition, it is a little bit easier to understand the recursion.

So, leaving this here in case it helps someone to understand a bit about CTEs.

Answer 2

This is for Hierarchical Query

with TableA as
(
    select 1 id, 'A1' Name, 'Key1' key from dual union all
    select 2, 'A2', 'Key2'   from dual union all
    select 3, 'A3', 'Key3'   from dual 
)
, tableb as
(
    select 1 id, 1 a_id, 'B1' name , null CONTAINER_A_ID from dual union all
    select 2 , 1 , 'B2'  , null from dual union all
    select 3 , 1 , 'B3'  , 2 from dual union all
    select 4 , 2 , 'B4'  , null from dual union all
    select 5 , 2 , 'B5'  , null from dual union all
    select 6 , 3 , 'B6'  , null from dual union all
    select 7 , 3 , 'B7'  , null from dual 

)
select 
    a.id, a.name, a.key, b.id, b.a_id, b.name, b.container_a_id
from 
    tableb b
left join 
    tablea a
on
    a.id = b.a_id
start with
    A.Key = 'Key1'
connect by 
    prior b.container_a_id = b.a_id;

If you need order then add order by a.id, b.id,a.name,...; to the end.

Answer 3

This is on Oracle 11g.

If you are specifically looking for CONNECT BY I am not aware of that yet.

drop table t1; drop table t2;
create table t1 (id int primary key, name char(5), key char(5));
create table t2 (id int primary key, a_id int, name char(5) , container int);

insert into t1 values (1, 'A1', 'K1');
insert into t1 values (2, 'A2', 'K2');
insert into t1 values (3, 'A3', 'K3');

insert into t2 values (1, 1, 'B1', null);
insert into t2 values (2, 1, 'B2', null); 
insert into t2 values (3, 1, 'B3', 2); 
insert into t2 values (4, 2, 'B4', null);
insert into t2 values (5, 2, 'B5', null);
insert into t2 values (6, 3, 'B6', null);
insert into t2 values (7, 3, 'B7', null);

with t(id, name, key, bid, aid, bname, con) as (
    select a.id, a.name, a.key, b.id, b.a_id, b.name, b.container
    from    t1 a
            inner join
            t2 b
            on a.id = b.a_id
            where a.key = 'K1'
    union all
           select a.id, a.name, a.key, b.id, b.a_id, b.name, b.container
           from  t t
           inner join
           t1 a
           on a.id = t.con
           inner join
           t2 b
           on a.id = b.a_id
) select * from t;

EDIT: Reponse to Jorge's comment

insert into t2 values (4, 2, 'B4', 3);