5 Replies Latest reply: Jan 29, 2013 4:44 AM by 690004 RSS

    hierchiacal queries help

    690004
      HI ALL,

      Can anyone please help me in getting parent child realtion for below scenario using start with connect by.

      create table test1 (dest1 number,source1 number);

      INSERT INTO TEST1 VALUES (22,15740);
      INSERT INTO TEST1 VALUES (22,15740);
      INSERT INTO TEST1 VALUES (22,15741);
      INSERT INTO TEST1 VALUES (22,15741);
      INSERT INTO TEST1 VALUES (22,15742);
      INSERT INTO TEST1 VALUES (22,15742);
      INSERT INTO TEST1 VALUES (22,15743);
      INSERT INTO TEST1 VALUES (22,15743);
      INSERT INTO TEST1 VALUES (22,15744);
      INSERT INTO TEST1 VALUES (22,15744);
      INSERT INTO TEST1 VALUES (22,15745);
      INSERT INTO TEST1 VALUES (22,15745);
      INSERT INTO TEST1 VALUES (22,15746);
      INSERT INTO TEST1 VALUES (22,15747);
      INSERT INTO TEST1 VALUES (22,15748);
      INSERT INTO TEST1 VALUES (22,15749);
      INSERT INTO TEST1 VALUES (22,15750);

      create table test2 (dest2 number,source2 number);

      INSERT INTO TEST2 VALUES (15740,23);
      INSERT INTO TEST2 VALUES (15740,24);
      INSERT INTO TEST2 VALUES (15741,25);
      INSERT INTO TEST2 VALUES (15741,26);
      INSERT INTO TEST2 VALUES (15742,27);
      INSERT INTO TEST2 VALUES (15742,28);
      INSERT INTO TEST2 VALUES (15743,29);
      INSERT INTO TEST2 VALUES (15743,30);
      INSERT INTO TEST2 VALUES (15744,31);
      INSERT INTO TEST2 VALUES (15744,32);
      INSERT INTO TEST2 VALUES (15745,33);
      INSERT INTO TEST2 VALUES (15745,34);
      INSERT INTO TEST2 VALUES (15746,35);
      INSERT INTO TEST2 VALUES (15747,36);
      INSERT INTO TEST2 VALUES (15748,11821);
      INSERT INTO TEST2 VALUES (15749,37);
      INSERT INTO TEST2 VALUES (15750,38);

      below sql query is returning 17 rows.

      I want to display these 17 rows using the hierachichal query.

      becasue we have more than one level

      select distinct a.dest1,B.source2 from TEST1 A,TEST2 B
      where A.SOURCE1=B.DEST2
      and A.DEST1=22;


      Thanks in advance.