getting all parents for a child

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)

getting all parents for a child

Author

Topic

kingjeremy
Starting Member

10 Posts

Posted - 2009-11-24 : 04:17:05

Hi,
I have a classic Id, parentId, col3, col4 type table and I'm trying to get all parentId's for a given Id all the way up to the top most level. Like
Id, parentId ...
1, 0
2, 1
3, 1
4, 3
5, 3
6, 4
For 6 I would get the 4, 3, 1, 0
Thanks for any help.

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-24 : 04:53:51

SELECT DISTINCT parentId FROM table WHERE id <= 6

Balaji.K

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-24 : 04:55:27

try this


declare @t table (Id int, parentId int)
insert into @t select 
1, 0 union all select 
2, 1 union all select 
3, 1 union all select 
4, 3 union all select 
5, 3 union all select 
6, 4

declare @id int
select @id = 6

;with cte (id,parentid)
as
(
select id,parentid from @t where id = @id 
 union all 
select t.id,t.parentid
from @t t join cte c on t.id = c.parentid and t.parentid <> c.parentid
)
select * from cte

kingjeremy
Starting Member

10 Posts

Posted - 2009-11-24 : 05:14:30

Thanks bklr for the quick reply this is what I needed.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-24 : 05:59:40

Welcome but it will works in 2005 and above versions only.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources