July 30, 2008 at 9:54 am
Hi members,
I joined this forum today. I have a table which is in tree model like
PID ParentID IndividualID
10 10 10
10 10 20
10 10 30
10 30 40
10 30 50
10 20 110
10 20 120
10 50 60
10 50 70
10 70 80
10 70 90
Now I need to find out all the individual ids, parentids,PIDs if only one individual id is given as input.
and load the individual ids in temp table.
for eg: if my input individual id 70 I need to find out 10,30,40,50,60,70,80,90 which are in that branch and load it into another table.
Pls respond to this question asap.
thanks
sai
July 30, 2008 at 10:07 am
HI There,
I'm not sure how if you have an input of 70 you get a result of 10,30,40,50,60,70,80,90
Please could you explain your input and out values again?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 30, 2008 at 10:42 am
July 30, 2008 at 11:17 am
Hi,
If my input is 70 with the help of PID i can trace that 10 is my top parentid and I need to traverse from 10 to find out the childids of 70 and the parentids and their parentids.
I think it would be clear if i give you the tree example of the data.
You can see the tree example in the attachment.
thanks,
sainath
July 30, 2008 at 11:30 am
what im seeing, is you have 70. You know 10 is top level. So you want the entire tree that 70 is in.
Not just the path in the tree (or 40, 60, 80 and 90 would be excluded)
Here's a hack job - it takes 30 queries just to get the results you want with a temp table. I just whipped it up to solve the puzzle, but im sure there's a better way to do it.
your table
create table #temp2 (PID int, ParentID int, IndividualID int)
insert #temp2
select 10, 10, 10 UNION ALL
select 10, 10, 20 UNION ALL
select 10, 10, 30 UNION ALL
select 10, 30, 40 UNION ALL
select 10, 30, 50 UNION ALL
select 10, 20, 110 UNION ALL
select 10, 20, 120 UNION ALL
select 10, 50, 60 UNION ALL
select 10, 50, 70 UNION ALL
select 10, 70, 80 UNION ALL
select 10, 70, 90
determine second level below top
declare @value int
declare @prevValue int
declare @topLevel int
set @value = 70
select @topLevel = PID from #temp2 where individualId = @value
WHILE @value != @topLevel
BEGIN
set @prevValue = @value
select @value=parentid from #temp2 where individualId = @value
END
find all the individualID's and add top level
create table #ids (id int)
insert #ids select @prevValue
declare @coun int
set @coun = 0
while @coun != (select count(distinct id) from #ids)
begin
select @coun = count(distinct id) from #ids
insert #ids
select distinct individualid
from #temp2
where parentid in (select * from #ids)
end
insert #ids select @topLevel
grab the data and drop the temp tables
select * from #temp2 where individualid in (select distinct id from #ids)
drop table #ids
drop table #temp2
again - there is more efficient ways to do this. A big tree will results in a TON of queries. I just tried to do it without using cursors.
results:
PIDParentIDIndividualID
101010
101030
103040
103050
105060
105070
107080
107090
July 30, 2008 at 11:32 am
You can use a recursive CTE.
I had to use a UNION to go back up the tree, but this seems to have worked,
[font="Courier New"]CREATE TABLE #t (PID INT, ParentID INT, IndividualID INT)
INSERT #t VALUES (10,10,10)
INSERT #t VALUES (10,10,20)
INSERT #t VALUES (10,10,30)
INSERT #t VALUES (10,30,40)
INSERT #t VALUES (10,30,50)
INSERT #t VALUES (10,20,110)
INSERT #t VALUES (10,20,120)
INSERT #t VALUES (10,50,60)
INSERT #t VALUES (10,50,70)
INSERT #t VALUES (10,70,80)
INSERT #t VALUES (10,70,90)
; WITH MyTab (ParentID, IndividualID)
AS (
SELECT ParentID, IndividualID FROM #t
UNION ALL
SELECT T.ParentID, X.IndividualID
FROM #t T
INNER JOIN MyTab X ON T.IndividualID = X.ParentID
WHERE T.IndividualID <> T.ParentID
)
SELECT
ParentID
FROM
MyTab
WHERE
ParentID = 70
OR IndividualID = 70
UNION
SELECT
IndividualID
FROM
MyTab
WHERE
ParentID = 70
OR IndividualID = 70[/font]
July 30, 2008 at 11:38 am
I was thinking a recursive query, but couldn't think of how to properly make it go back down the tree to find the remaining items... the recursive query misses 40 and 60.
July 30, 2008 at 11:47 am
I think the original post was wrong.
If you look at the diagram and the data, 40 and 60 are children of 30 and 50, not in the path we are trying to include. We want 70's children and all of it's parents. I don't think we want siblings or cousins.
Otherwise, wouldn't 20 need to be included?
July 30, 2008 at 11:54 am
Ya, I don't know. I read it differently. It wasn't very clear.
I assume PID was the tree, and the second level was the branch. And the results were to be all items in branch it belonged to. Hence having the whole 20 branch discarded with that tree.
Anyways, I like the way that CTE recursion works. Will be useful for this application I am taking over that use unlimited child->parent linking.
July 30, 2008 at 11:59 am
Just beware of it.
It ends up pretty slow because it typically ends up with a looping cursor in the background - so it is row-by-row processing.
But in cases of recursion like this, all of the options are slow, so the one that looks nice wins out for me.
July 30, 2008 at 1:53 pm
;with
HierarchyDown (ID, ParentID) as
(select individualid, parentid
from dbo.Table
where individualid = @InputParam_in
union all
select t2.individualid, t2.parentid
from dbo.Table t2
inner join HierarchyDown hd
on t2.parentid = hd.id),
HierarchyUp (ID, ParentID) as
(select individualid, parentid
from dbo.Table
where individualid = @InputParam_in
union all
select t3.individualid, t3.parentid
from dbo.Table t3
inner join HierarchyUp hu
on t3.id = hu.parentid)
select *
from hierarchydown
union
select *
from hierarchyup
That should get you what you're looking for.
- 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
July 30, 2008 at 2:14 pm
Hi,
I think even if i miss 40 and 60 also there is no problem. Can u pls send the recursive query for that.
thanks,
sainath
July 30, 2008 at 2:17 pm
Hi,
I think you are absolutely right and I confirmed that 40 and 60 are also not needed for me. I really appreciate your help.
If u can find any solution pls let me know.
Thank you,
sainath
July 30, 2008 at 2:41 pm
Is the query I wrote not doing what you need?
- 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 4, 2008 at 9:12 am
Hi,
When I was trying the hierarchyup CTE and it was going not stoping on reaching at the top level.
can you please look into that give some solution
thanks,
sainath
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply