CODEWITHSUNDEEP
sqloracle-database
Ahmad
Ahmad

Reputation: 12737

How to obtain Child-Parent relationship from multiple tables in Oracle?

==

SQL Fiddle of My Question Here

I have the following tables related to a steel factory 

HEATS /* Contains data about raw iron melted from scrap and ores */
SLABS /* Contains data about the output of the first table HEATS */
COILS /* Contains data about the output of SLABS */

I have simplified the structure of the above tables by removing un-necessary fields un-related to the question

create table heats
  ( id number,
    production_date date, 
    heat_name varchar(10),
    parent number
  );

create table slabs
  ( id number,
    production_date date, 
    slab_name varchar(10),
    parent number
  );


create table coils
  ( id number,
    production_date date, 
    coil_name varchar(10),
    parent number
  );

I also inserted some dummy data (but with proper relation-ships) like this:

insert into heats values (1,'01-Nov-2012','GRADE A',null);  
insert into heats values (2,'01-Nov-2012','GRADE B',null);  
insert into heats values (3,'01-Nov-2012','GRADE C',null);  

insert into slabs values (10,'02-Nov-2012','SLAB A',1);
insert into slabs values (20,'02-Nov-2012','SLAB B',2);
insert into slabs values (30,'02-Nov-2012','SLAB C',3);

insert into coils values (100,'03-Nov-2012','COIL A.1',10);
insert into coils values (200,'03-Nov-2012','COIL B.1',20);
insert into coils values (300,'03-Nov-2012','COIL C.1',30);

insert into coils values (400,'03-Nov-2012','COIL A.2',100);
insert into coils values (500,'03-Nov-2012','COIL B.2',200);
insert into coils values (600,'03-Nov-2012','COIL C.2',300);

insert into coils values (700,'03-Nov-2012','COIL A.3',400);
insert into coils values (800,'03-Nov-2012','COIL B.3',500);
insert into coils values (900,'03-Nov-2012','COIL C.3',600);

Notice in the last 9 INSERTS, some coils can be children of other coils, and some coils can be the children of slabs. Slabs can only be children of Heats. Heats have no parent.

Now, I want to obtain the family tree of the coils COIL A.3. I can simply obtain child-parent relationship between coils and coils. like this

select coil_name from coils c
start with coil_name='COIL A.3'
connect by prior c.parent = c.id

That works fine, and I get the output

COIL A.3
COIL A.2
COIL A.1

But I want the output to also include parents from other tables (heats and slabs)

COIL A.3
COIL A.2
COIL A.1
SLAB A
HEAT A

but when I try to add the other table names into the query and modify the connect by clause, the query gets irritatingly slow. How can I achieve the desired output more efficiently?

Upvotes: 2

Views: 2274

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27261

Union all result sets of queries:

 SQL> select *
  2    from
  3  (
  4    select id
  5         , slab_name as name
  6         , parent
  7      from slabs c
  8  
  9    union all
 10  
 11    select id
 12         , coil_name
 13         , parent
 14      from coils c
 15  
 16    union all
 17  
 18    select id
 19         , heat_name
 20         , parent
 21      from  heats
 22  
 23  
 24    )
 25   start with name='COIL A.3'
 26   connect by prior parent = id
 27  ;

        ID NAME           PARENT
---------- ---------- ----------
       700 COIL A.3          400
       400 COIL A.2          100
       100 COIL A.1           10
        10 SLAB A              1
         1 GRADE A

Upvotes: 2

Related Questions