help for recursive query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help for recursive query

Author

Topic

tiffiny
Starting Member

7 Posts

Posted - 2007-07-05 : 12:03:09

I have a table that represents a tree structure via a self-join. e.g. given

id parentid
1 NULL
2 1
3 2
4 2
5 1
6 4
7 NULL
8 7
9 7
10 9
11 10

I need to get the root of the given child.
for example: if the given child is 6 then return 1. if child=10 then return 7. I need this in a single query only, is it possible?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-05 : 12:17:19

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

Madhivanan

Failing to plan is Planning to fail

tiffiny
Starting Member

7 Posts

Posted - 2007-07-05 : 12:30:32

quote:
Originally posted by madhivanan

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

Madhivanan

Failing to plan is Planning to fail

The recursive example given in http://msdn2.microsoft.com/en-us/library/ms186243.aspx only for the whole structure of the tree result. It can't produce what i expected also. thanks Madhivanan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-05 : 13:56:41

I think this is what you are looking for. Note: you should be able to replace the hard coded 7 with a variable if that is what you are trying to get to:

DECLARE @T TABLE (ID INT, ParentID INT)

INSERT @T
SELECT 1, NULL
UNION ALL SELECT 2, 1
UNION ALL SELECT 3, 2
UNION ALL SELECT 4, 2
UNION ALL SELECT 5, 1
UNION ALL SELECT 6, 4
UNION ALL SELECT 7, NULL
UNION ALL SELECT 8, 7
UNION ALL SELECT 9, 7
UNION ALL SELECT 10, 9
UNION ALL SELECT 11, 10
;

WITH Parent 
AS
(
	SELECT ID, ParentID
	FROM @T t
	WHERE ID = 7

	UNION ALL
	
	SELECT t1.ID, t1.ParentID
	FROM @T t1
	INNER JOIN Parent AS p
	ON t1.ParentID = p.ID
)
SELECT *
FROM Parent
WHERE ParentID IS NULL

-Ryan

DaleJ
Starting Member

7 Posts

Posted - 2007-07-05 : 14:57:24

See if this suits the need...

DECLARE @T TABLE (ID INT, ParentID INT)

INSERT @T
SELECT 1, NULL
UNION ALL SELECT 2, 1
UNION ALL SELECT 3, 2
UNION ALL SELECT 4, 2
UNION ALL SELECT 5, 1
UNION ALL SELECT 6, 4
UNION ALL SELECT 7, NULL
UNION ALL SELECT 8, 7
UNION ALL SELECT 9, 7
UNION ALL SELECT 10, 9
UNION ALL SELECT 11, 10
;

with uptheladder(anchor, id, parentid, lvl)
as
(
select id, t1.*, 0
from @t t1
union all
select anchor, t2.*, lvl + 1
from @t t2
inner join uptheladder l
on t2.id = l.parentid
),
ladderchain
as
(
select anchor, max(lvl) as lvl
from uptheladder
group by anchor
),
laddertop
as
(
select l.anchor as id, l.id as parentid
from uptheladder l
inner join ladderchain c
on l.anchor = c.anchor
and l.lvl = c.lvl
)

select id, parentid
from laddertop
where id = 10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources