This discussion is archived
5 Replies Latest reply: Jan 29, 2013 2:44 AM by 690004 RSS

hierchiacal queries help

690004 Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points