Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 23 Jul 2010 @ 13:14:45 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Child/Parent table, getting all the descendents of a parent
 
From:   cblake

Hello everyone -

I have a situation where I've got a child parent like table, and I don't know how many possible levels there are, and I'd like to get all the parent's children. Is there any way to do something like this?

Here is an example, and the path I've gone down:

     CREATE VOLATILE TABLE vt
     (processId int
     , inputProd VARCHAR(10)
     , outputProd VARCHAR(10)
     , qtyUsed int
     ) ON COMMIT PRESERVE ROWS;

     bt;
     insert into vt values(101, 'paper', 'JumboR', 1000);
     insert into vt values(101, 'glue', 'JumboR', 500);
     insert into vt values(101, 'core', 'JumboR', 1);
     insert into vt values(201, 'JumboR', 'RedTape', 1);
     insert into vt values(201, 'DyeRed', 'RedTape', 200);
     insert into vt values(202, 'JumboR', 'GreenTape', 1);
     insert into vt values(202, 'DyeGreen', 'GreenTape', 200);

     insert into vt values(203, 'GreenTape', 'DecoTape1', 1);
     insert into vt values(203, 'Overlay1', 'DecoTape1', 1);

     insert into vt values(901, 'JumboR', 'JumboR', 1);
     insert into vt values(902, 'JumboR', 'paper', 1);
     insert into vt values(902, 'chemical', 'paper', 1);
     et;


     /*
     select *
     from vt;

     select vt.inputProd, vt.outputProd, vt2.outputProd
     from vt
     left outer join (
             select inputProd, outputProd
             from vt
             group by 1,2
     ) vt2
     on vt2.inputProd = vt.outputProd
     group by 1,2,3
     */

     /* This method would work, but I'd have to assume a specific number of
     levels, and continue to nest these "or" statements */
     select outputProd
     from vt
     where (inputProd = 'paper'
             or inputProd in (
                     select outputProd
                     from vt
                     where inputProd = 'paper'
                     or inputProd in (
                             select outputProd
                             from vt
                             where inputProd = 'paper'
                     )
             )
     );

     drop table vt;

Thank you in advance for any help you can offer!


Chris



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016