hierarchy query
am 13.01.2008 05:20:13 von Tem
I have a table that looks like this. Each row is a reply or a topic when
IsAReplyTo = 0
I need to write a query that pulls all rows of a hierarchy of a given ID
Table Forum
ID IsAReplyTo
4 0
9 4
27 4
63 27
73 0
82 63
given @ID = 63 it should return ID 4,9,27,82
Im not sure where to go from here.
Select * from Forum where IsAReplyTo = @ID
can it be done with a single query?
Tem
Re: hierarchy query
am 13.01.2008 05:28:29 von Mark Fitzpatrick
If you're using SQL Server 2005, you can use Common Table Expressions (CTEs)
to handle the recursive hierarchy. MS has some info about them at:
http://msdn2.microsoft.com/en-us/library/ms190766.aspx
and http://msdn2.microsoft.com/en-us/library/ms186243.aspx
They're a tad tricky to master, but once you get it down they can be
amazing.
--
Hope this helps,
Mark Fitzpatrick
Microsoft MVP - Expression
"Tem" wrote in message
news:uRESeuZVIHA.5208@TK2MSFTNGP04.phx.gbl...
>I have a table that looks like this. Each row is a reply or a topic when
>IsAReplyTo = 0
> I need to write a query that pulls all rows of a hierarchy of a given ID
>
> Table Forum
> ID IsAReplyTo
> 4 0
> 9 4
> 27 4
> 63 27
> 73 0
> 82 63
>
> given @ID = 63 it should return ID 4,9,27,82
>
> Im not sure where to go from here.
> Select * from Forum where IsAReplyTo = @ID
>
> can it be done with a single query?
>
> Tem