August 8, 2008 at 4:17 am
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]:)
August 8, 2008 at 5:22 am
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
August 8, 2008 at 5:27 am
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.
August 8, 2008 at 12:34 pm
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
August 8, 2008 at 9:54 pm
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]:)
August 8, 2008 at 10:14 pm
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]:)
August 9, 2008 at 3:47 am
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