Retreiving the Childs in a single statement.

  • Hi SQL Experts,

    I have a table with the below structure:

    Pri_ID RefID ParentID

    1 Random1 NULL

    ......

    10 Random2 Random1

    ......

    34 Random3 Random2

    Like the above a single parent can have any no of child and sub child.

    Now I am having only the parent and I have to take all the child of the parent.

    For this I have planed to loop by checking the existence of the child.

    But this will be heavy when the no of parents is huge.

    Can any one tell me the feasible way to do it?

    Thank you a lot in advance.

    [font="Arial"]Nothing is impossible with Hard Work[/font]:)

  • If I understood you right, you can use a recursive query to get all the children of a particular node:

    WITH closedMyTable ( Pri_ID, ReflID, ParentID )

    AS ( SELECT Pri_ID

    , ReflID

    , ParentID

    FROM myTable

    WHERE ParentID = Random1

    UNION ALL

    SELECT b1.Pri_ID

    , b1.ReflID

    , b2.ParentID

    FROM myTable AS b1

    INNER JOIN closedMyTable AS b2 ON b1.ParentID = b2.ReflID

    )

    SELECT Pri_ID

    , ReflID

    FROM closedMyTable

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Hi,

    Can you be more precise in your table structure..

    i.e

    Pri_ID RefID ParentID

    1 Random1 NULL

    ......

    10 Random2 Random1

    ......

    34 Random3 Random2

    you have parentid but you have mentioned like random1 etc..

    and can you tell me your requirement with some sample data &

    the o/p required.

    Thanks.

  • Andras' solution should do it.

    The other option would be to modify it to a nested sets hierarchy (Joe Celko style) and query that way.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Dear Chandru

    Thank you for the response.

    The Random Id are unique identifier.

    Actually, I have a parent item with an unique identifier.

    When the parent is revised then the child will have the parent ID as the parent's unique identifier and it will have its own identifier.

    When the child is revised then its child will have the parent Id as the Child's identifier and it has this own identifier.

    Like this the thread will continue.

    Thanks.

    [font="Arial"]Nothing is impossible with Hard Work[/font]:)

  • Dear Friend,

    Thank you a lot it is working.

    Thank you so much.

    Best Regards,

    M. J. Jaya Chitra

    [font="Arial"]Nothing is impossible with Hard Work[/font]:)

  • Jaya Chitra (8/8/2008)


    Nothing is impossible with Hard Work 🙂

    Well, nothing is as hard as posting a question instead of reading Books Online.


    N 56°04'39.16"
    E 12°55'05.25"

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply