5 Replies Latest reply: Jan 29, 2013 10: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.
        • 1. Re: hierchiacal queries help
          Girish Sharma
          Thanks for providing create table and insert statement. Just tell us what is your required output with clear and simple logic behind it. Take your own time to elaborate the logic behind required output, so that we may understand what do you want.

          Again, don't be in hurry, we are here 24x7 and this forum is running 24x7.... :)

          Regards
          Girish Sharma
          • 2. Re: hierchiacal queries help
            Padma....
            Hi,

            You cannot use connect by and start with clauses related to heirarchical query on a select having a join of multiple tables. Instead merge the tables data and then you can go for a heirarchical query.

            Please post your requirement so that alternative solution can be provided.

            Thanks
            Padma...
            • 3. Re: hierchiacal queries help
              690004
              Hi,

              I got the solution for this using hierrchial query

              Thanks,
              • 4. Re: hierchiacal queries help
                Girish Sharma
                Thats good to hear you got the solution, but will you please share the query, output and logic for future readers if some other reader want to get help from your solution.

                Also please close the thread too.

                Regards
                Girish Sharma
                • 5. Re: hierchiacal queries help
                  690004
                  This is the sql query to get first parent and last child.

                  SELECT DISTINCT A.*
                  FROM
                  (SELECT DEST_ID,SOURCE_ID, CONNECT_BY_ISLEAF AS LEAF,level ll,CONNECT_BY_ROOT source_id AS parent_id
                  FROM MN_MAP_EDGE
                  START WITH DEST_ID =1
                  CONNECT BY PRIOR source_id=dest_id) a
                  where leaf=1