February 4, 2010 at 3:33 am
Hi,
I have a table that holds two IDs in it. One is the parent and one is the child. A child can then be a parent of another child and there is not limitation (in theory) of how far this inheritance could go.
What I need to do is write a procedure/function that will get the full history from an ID, but not knowing where in the line it would be (i.e. the end, beginning or somewhere in the middle).
My question, is what's the best way to do this, without impacting to much on the database. I thought of a couple of WHILE loops, firstly to get the original parent and then to get all the children.
I'm aware this could be done using recursion, but I'm not sure if this is a little overkill from what I'm trying to do?
Any thoughts would be appreciated.
February 4, 2010 at 7:40 am
Hi Tom,
I would use a CTE to do the recursion. In your case you want to recurse both up and down in the hierarcy, so you would need two CTE:s to avoid eternal loops.
This example shows one way to solve it:
declare @id int
set @id = 6 -- 5
create table #t
(
id int,
parent int
)
insert into #t values (1, NULL)
insert into #t values (2, NULL)
insert into #t values (3, 1)
insert into #t values (4, 1)
insert into #t values (5, 2)
insert into #t values (6, 3)
insert into #t values (7, 5)
insert into #t values (8, 6)
insert into #t values (9, 6)
insert into #t values (10, 8)
-- This CTE search upwards for the parents
;WITH x (id, parent, [level])
AS
(
select id, parent, 0
from #t
where id = @id
UNION ALL
select t.id, t.parent, x.level - 1
from #t as t inner join
x as x on t.id = x.parent
)
select *
into #result
from x
-- this CTE search for the (grand)children of the node
;WITH x (id, parent, [level])
AS
(
select id, parent, 1
from #t
where parent = @id
UNION ALL
select t.id, t.parent, x.level + 1
from #t as t inner join
x as x on x.id = t.parent
)
insert into #result
select *
from x
-- display result
select *
from #result
order by [level]
drop table #t
drop table #result
/Markus
February 4, 2010 at 8:19 am
Here's an article I wrote a while back on hierarchies in SQL. The article and the discussion cover the subject pretty well.
http://qa.sqlservercentral.com/articles/T-SQL/65540/
- 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
February 4, 2010 at 3:40 pm
Nothing specific in the form of code to offer on this but I've found that recursive CTE's are slower than While Loops and they generate a whole lot more reads.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply