Hierarchy query with 3 tables

Question

I am not able to understand how to achieve a hierarchy accessing 3 tables. That is, I have Table1, Table2 and Table3 where:

Table1 (ID_table1, name_c, size)

Table2 (ID_table2, ID_table1, name_l, size)

Table3 (ID_table3, ID_table1, ID_table2, name_d, size)

How do I make a hierarchy with all this information? I've looked for it but only found solutions with information into a single table.

*EDIT: I edited the information in the tables because they do not express myself

Answer 1

I've never tried anything like that, but what if you UNION your three tables into one, and then do a "one-table hierarchical query" on the result of that?

WTIH alltabs as
    (Select 'T1' as src, Table1.ID, NULL AS ID_table1, NULL as ID_table2, Table1.name, Table1.size
    from Table1
    union
    Select 'T2' as src, Table2.ID, Table2.ID_table1, NULL as ID_table2, Table2.name, Table2.size
    from Table2
    union
    Select 'T3' as src, Table3.ID, Table3.ID_table1, Table3.ID_table2, Table3.name, Table3.size
    from Table3)
Select *
from alltabs
/*you can have an optional STARTING WITH clause here, but it's not clear from your question what that would be*/
connect by (id = id_table1 and (src = 'T1' or src = 'T2'))
        or (id = id_table2 AND (src = 'T2' or src = 'T3'))

(I'm assuming that the relationship is Table1 PARENT OF Table2 PARENT OF Table3).

Answer 2

Similar to Frusterated's answer⁺¹, but with a few different assumptions.

drop table table1;
drop table table2;
drop table table3;

create table table1 (ID Number(3), Name Varchar2(10), SizeX number(3));
create table table2 (ID Number(3), ID_table1 Number(3), Name Varchar2(10), SizeX number(3));
create table table3 (ID Number(3), ID_table2 Number(3), Name Varchar2(10), SizeX number(3));

insert into table1 values (1,'a',1);
   insert into table2 values (10, 1, 'a-a', 3);
   insert into table2 values (11, 1, 'a-b', 4);
      insert into table3 values (100, 11, 'a-b-a',8);
      insert into table3 values (101, 11, 'a-b-b',9);
insert into table1 values (2,'b',2);
   insert into table2 values (12, 2, 'b-a', 5);
   insert into table2 values (13, 2, 'b-b', 6);
      insert into table3 values (102, 13, 'b-b-a',10);
   insert into table2 values (14, 2, 'b-c', 7);
commit;

SELECT id, parent, name, sizex, level
FROM
(
   SELECT id, null parent, name, sizex FROM Table1
   UNION ALL
   SELECT id, id_table1, name, sizex FROM TABLE2
   UNION ALL 
   SELECT id, id_table2, name, sizex FROM TABLE3
)
START WITH parent IS NULL
CONNECT BY (prior id = parent);