Results 1 to 8 of 8

Thread: query output

  1. #1
    Join Date
    Mar 2008
    Posts
    136

    Unanswered: query output

    Hi All,
    There is one table and have self reference . I want some output from that table as per some condition.

    Table A---
    Records Like--
    PKAID FKAID
    1 NULL
    2 1
    3 1
    4 3
    5 2
    6 NULL
    7 5

    If I give input PKID=1 then output must be like below as I need only PKAID..

    PKAID
    1
    2
    3
    4
    5
    7

    If I give input PKID=3 then output must be like below as I need only PKAID..

    PKAID
    3
    4

    If I give input PKID=6 then output must be like below as I need only PKAID..

    PKAID
    6

    It seems here will be used recursive type query or pl/sql but how not getting
    Please suggest some solution for the same...

    Regards
    Regards
    Pawan Kumar

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Pawan Kumar View Post
    PKAID FKAID
    ....

    If I give input PKID=1 then output must be like below as I need only PKAID..
    Your (incomplete) table description does not have a column named PKID

  3. #3
    Join Date
    Mar 2008
    Posts
    136
    sorry thats PKAID....
    Regards
    Pawan Kumar

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Try something like this
    Code:
    SELECT pkaid
    FROM table_with_no_name
    CONNECT BY PRIOR pkaid = fkaid
    START WITH pkaid = 3;
    Change the START WITH condition to match your root node

  5. #5
    Join Date
    Mar 2008
    Posts
    136
    thanks but is there any alternative way.....???
    Regards
    Pawan Kumar

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Pawan Kumar View Post
    thanks but is there any alternative way.....???
    Yes, but only with the latest Oracle version which is 11g Release2
    There you can use the standard ANSI syntax with recursive common table expressions (which also works with nearly every other database except MySQL)
    Code:
    WITH RECURSIVE depTree (pkaid, fkaid) as 
    (
      SELECT pkaid, fkaid
      FROM table_with_no_name
      WHERE pkaid = 1
      
      UNION ALL
      
      SELECT t2.pkaid, t2.fkaid
      FROM table_with_no_name t2 
        INNER JOIN depTree ON depTree.pkaid = t2.fkaid
    )
    SELECT pkaid
    FROM depTree;
    Just change the WHERE pkaid = 1 in the first part of the union to define your "starting point"

  7. #7
    Join Date
    Mar 2008
    Posts
    136
    thanks....I need general logic because application runs on mysql and oracle both....
    Regards
    Pawan Kumar

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Pawan Kumar View Post
    thanks....I need general logic because application runs on mysql and oracle both....
    If you have to cope with the limited features of MySQL then you will need to either write a procedure to retrieve this or use a different approach for storing the hierarchy e.g. using nested sets - which is explained in the MySQL manual.

    Good luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •