I came up with a method of retrieving all descendants of a 'category' today by joining into a custom function, and I thought I'd share it online.
Let's say your user can select up to 5 levels of categories in the search page, and you need to match all products that belong to any of the categories beneath that category. (I'm also going that we know that cursors are evil).
The method I'm presenting allows you to join the results of a function instead of doing a 'categoryid=X' WHERE statement, like so:
Now, let's have a look at the DescendantCategories function:
So there it is; I hope you find good use for it. It's very fast and it even will circumvent infinite loops that could occur with circular hierarchy references.