I have a SQL 2000 table, tblCategories, that includes an id field (PK, int) and a parent_id field. Each row represents a category in a hierarchically-organized catalog.
I need to support queries that consider one of the nodes in the tree and all of it's children (like everything under "Men's Apparel", even if there are many levels below it).
If I could write a user-defined function that accepts the id of a category and returns a 1-column table that includes that id and all the id's of nodes underneath it (even multiple layers deep), then I could simply join the call to that function with the other tables/data I'm interested in.
A) Any feedback on the overall approach?
B) If it's a plausible approach, any help with writing the function?
Thanks for any help-