Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unanswered: How to get parent from a given child node, using Connect by clause

    Hi,
    I am in trouble of using "Connect by" clause, I got a tree which has parent and child relationship. For a given parent, I know how to get the child:

    select child FROM table
    start with parent = null
    connect by prior child = parent;

    However, is there any way i can get the parents, grandparents, all the way up to the top for a given child? Thanks in advance.

    Kind regards,
    Adrian

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, just do it the other way around:

    select child FROM table
    start with child = "baby"
    connect by prior parent = child;

  3. #3
    Join Date
    Oct 2004
    Posts
    2
    Oh, it works, thx a lot.

  4. #4
    Join Date
    Feb 2009
    Posts
    3
    Hi ,
    I have used this query I got all parents and grand parents. Thats good. But how can I know the grand parent of the given chaild?

    Please help me!!!

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    select child
    FROM table
    start with child = 'baby'
    connect by prior parent = child
    where level = 2

  6. #6
    Join Date
    Feb 2009
    Posts
    3
    Thank you! This will perfectly work for single chaild. How can we get the grand parents for multiple chailds.

    Let me explains the poblem

    |Parent | Chaild|
    -------------------
    101 | 102
    102 | 103
    103 | 104
    105 | 106
    106 | 107
    108 | 109
    109 | 110

    Here we have 3 grand parents 101,105,108

    For example
    If I give a list of chailds 103,107,110 my query should return corresponding grand parents 101,105,108 .

    Thanks you for your help shammat

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Try
    Code:
    select child
    FROM table
    start with child in (103,107,110)
    connect by prior parent = child
    where level = 2
    (Not tested)

  8. #8
    Join Date
    Feb 2009
    Posts
    3
    Here we know that parent at second level, If we dont know the level how can we write a query?

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by podile
    Here we know that parent at second level, If we dont know the level how can we write a query?
    I'm not sure I understand the question.

    If you want to find the root of the hierarchy something like this could work:
    Code:
    SELECT * 
    FROM (
       SELECT child, parent
       FROM table
       START WITH child IN (103,107,110)
       CONNECT BY PRIOR parent = child
    ) 
    WHERE parent IS NULL

Posting Permissions

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