hierarchial 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 2008 Forums

Transact-SQL (2008)

hierarchial query

Author

Topic

noamg
Posting Yak Master

215 Posts

Posted - 2012-01-12 : 11:25:26

Hi,
I need to get the whole nodes, full tree) under a specific node.
with 1 select statement

CREATE TABLE [dbo].[table2](
[child] [int] NOT NULL,
[parent] [int] NULL
)

for child = 2, get whole tree

Noam Graizer

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-01-12 : 12:33:02

You can use a recursive common table expression for this. Something like:


;with cte as 
(
	select	parent, child, 1 as lev
	from table2
	where child=2

	union all

	select t.parent, t.child, c.lev + 1
	from	cte c
	join	table2 t on t.parent = c.child
) 
select * from cte

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources