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

  • 2
    What kind of hierarchies would you want to build? Standard parent-child relationships are build in a single table. And hierarchies in that idea are on multiple levels (think manager-employees relations). What data structures do you like to model with your tables? – Marian Nov 13 '12 at 18:36
  • 2
    You need to exaplain the relationship better, is it Table1 PARENT OF Table2 PARENT OF Table3? – FrustratedWithFormsDesigner Nov 13 '12 at 18:48
  • @Marian, I want to build a hierarchy in which table1 stick with level 1, Table2 and Table3 as Level2 as level 3 – zmki Nov 14 '12 at 12:53

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
    Select 'T2' as src, Table2.ID, Table2.ID_table1, NULL as ID_table2, Table2.name, Table2.size
    from Table2
    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).

| improve this answer | |
  • excellent query but I needed an improvement if it were possible. And I have to read all this information in php and I have to pass first the father and correspondingly their children, and so and then I can move on to other parent. If I understand what I mean? Let's think in level 1 have the A and D, and at level 2 we have that A causes B and C and D do not originate anything. This query you created, first shows of all level 1 and level 2 of them then, and I wanted him to give me the level 1 when corresponding with their children. Got it? – zmki Nov 15 '12 at 10:54
  • @zmki: Oh that's a little different... Not sure the best way to do that. You might have to change the query in the WITH clause to use INNER JOINS between the tables instead of unions, but then you'd also have to think carefully about what data you select. Hmm... Or maybe you could add an ORDER BY in alltabs to correct it...? – FrustratedWithFormsDesigner Nov 15 '12 at 15:38

Similar to Frusterated's answer+1, 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);

SELECT id, parent, name, sizex, level
   SELECT id, null parent, name, sizex FROM Table1
   SELECT id, id_table1, name, sizex FROM TABLE2
   SELECT id, id_table2, name, sizex FROM TABLE3
CONNECT BY (prior id = parent);
| improve this answer | |
  • I love the whitespace utilization for hierarchy simulation :). Thumb up! – Marian Nov 13 '12 at 21:03
  • 1
    This will work only with the additional assumption that the ids are unique accross the 3 tables. – Vincent Malgrat Nov 14 '12 at 9:42
  • @Leigh Riffel I wrote id and name in both tables, but for different tables they have different names. I'll edit the initial post to best see fit – zmki Nov 14 '12 at 13:01
  • Renaming the name column is a simple thing, but Vincent is correct - the ids must be unique across tables for this to work. – Leigh Riffel Nov 14 '12 at 13:07
  • 1
    I'm sorry this does not work for you. What is different about your data or intended results that is not represented in the demonstration above? I created an SQL Fiddle for it as well -> sqlfiddle.com/#!4/423f7/1 – Leigh Riffel Nov 15 '12 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.