hierarchy query

[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

 

[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.

 

[--CELKO--]

can it be done with a single query? <<

Yes. Look up the nested sets model for a quick way to do this without
recursive programming.