5 Replies Latest reply on Oct 4, 2013 6:17 PM by ranit B

    Need help with query

    874450

      | Name     | ID | PARENT_ID |

      -----------------------------

      | a1       | 1  | 4      |

      | b2       | 2  | 5      |

      | c3       | 3  | 8      |

      | a1.d4    | 4  | 9         |

      | a1.e5    | 5  | 11         |

      | a1.d4.f6 | 6  | 13         |

      | a1.d4.g7 | 7  | 17         |

      | a1.e5.h8 | 8  | 21         |

      | a2.i9    | 9  | 25         |

      | a2.i9.j10| 10 | 16         |

       

      I would like to get result as if my input is 1

       

       | ID | PARENT_ID |
       1  | 4      |
      4  | 9      |
       9  | 25      |

       

      If my input is 2 then

      2 - 5

      5 - 11

        • 1. Re: Need help with query
          ranit B

          Did you read anything about "Hierarchial Queries" or techincally "Connect By Prior" ?

           

          Something like this:

          ranit@XE11GR2>> with t as(

            2     select 1 id, 4 parent from dual UNION ALL

            3     select 2 id, 5 parent from dual UNION ALL

            4     select 3 id, 8 parent from dual UNION ALL

            5     select 4 id, 9 parent from dual UNION ALL

            6     select 5 id, 11 parent from dual UNION ALL

            7     select 6 id, 13 parent from dual UNION ALL

            8     select 7 id, 17 parent from dual UNION ALL

            9     select 8 id, 21 parent from dual UNION ALL

          10     select 9 id, 25 parent from dual UNION ALL

          11     select 10 id, 16 parent from dual

          12  )

          13  select

          14     id, parent

          15  from t

          16  start with id = &id

          17  connect by PRIOR parent = id;


          Enter value for id: 1

          old  16: start with id = &id

          new  16: start with id = 1

           

                  ID     PARENT

          ---------- ----------

                   1          4

                   4          9

                   9         25

           

          HTH

          -- Ranit

          1 person found this helpful
          • 2. Re: Need help with query
            Frank Kulash

            Hi,

             

            That looks like a job for CONNECT BY.

             

            SELECT  id, parent_id

            FROM    table_x

            START WITH  id         = 1      -- or 2, or whatever

            CONNECT BY  parent_id  = PRIOR id

            ;

            I hope this answers your question.
            If not, post  a little sample data (CREATE TABLE and INSERT statements), and also post the results you want from that data (if not what you already posted).
            Point out where the query above is giving the wrong results, and explain, using specific examples, how you get the correct results from the given data in those places.  If you changed the query at all, post your code.

            Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

            See the forum FAQ: https://forums.oracle.com/message/9362002

            1 person found this helpful
            • 3. Re: Need help with query
              Warren Tolentino

              this will give some ideas.

               

              SQL> with vt as
                2  (select 'a1'        name,   1 id,  4 parent_id from dual union all
                3   select 'b2'        name,   2 id,  5 parent_id from dual union all
                4   select 'c3'        name,   3 id,  8 parent_id from dual union all
                5   select 'a1.d4'     name,   4 id,  9 parent_id from dual union all
                6   select 'a1.e5'     name,   5 id, 11 parent_id from dual union all
                7   select 'a1.d4.f6'  name,   6 id, 13 parent_id from dual union all
                8   select 'a1.d4.g7'  name,   7 id, 17 parent_id from dual union all
                9   select 'a1.e5.h8'  name,   8 id, 21 parent_id from dual union all
              10   select 'a2.i9'     name,   9 id, 25 parent_id from dual union all
              11   select 'a2.i9.j10' name,  10 id, 16 parent_id from dual)
              12  select lpad(' ',2*(level-1)) || id id, parent_id
              13    from vt
              14  connect by id = prior parent_id;

               

              ID                                                                                PARENT_ID
              -------------------------------------------------------------------------------- ----------
              1                                                                                         4
                4                                                                                       9
                  9                                                                                    25
              2                                                                                         5
                5                                                                                      11
              3                                                                                         8
                8                                                                                      21
              4                                                                                         9
                9                                                                                      25
              5                                                                                        11
              6                                                                                        13
              7                                                                                        17
              8                                                                                        21
              9                                                                                        25
              10                                                                                       16

              15 rows selected

              SQL>

              • 4. Re: Need help with query
                874450

                Thank you so much...

                • 5. Re: Need help with query
                  ranit B

                  Glad that it helped, but did you get the concept of Hierarchial queries clearly?

                   

                  If "YES", then I'll ask you a simple question... Is that fine? (Don't get scared )