But you still can do it in SQL2000, by scanning the whole table and calling a recursive routine to check whether that row has a particular parent or child, but for larger tables it is awfully resource intensive.

Then there are the approaches that iterate through the hierarchy levels until no records are retrieved anymore, that perform more reasonble.


Hmmm...and you were saying something a while ago about "being efficient"? :)

Another even better performing approach.. , just google on Joe Celko and Hierarchical trees and find he has a better way of optimizing those queries by having extra columns that are updated with triggers and allow you do select upon those columns direclty with straight index seeks and scans.

Depending on the installation, that could be the WORST solution of all. E-commerce vendors were ecstatic about CTEs and recursive queries, because it make it easier to work with hierarchical data that was different in structure for every installation. Can you imagine implementing something to deal with a different hierarchy for eveyr client...or worse, if someone's hierarchy changes? With all due respect to Mr. C, that's about the last thing I'd try.