Advanced SQL - Displaying Heirarchial Data

I am trying to display a heirarchial category structure from a single table in my database. The database consists of the following fields: category_id, category_name, category_parent_id. The depth should (preferrably) be unlimited. Although I would prefer to generate everything with a single SQL query - I am open to using a series of functions in ASP if this is not possible. I would also be open to developing a new category structure that is more efficient (if there is such a thing). Here are the operations that I need to perform: (1) Display heirarchial list of categories for building a DHTML menu system - obviously I would need to know the level of each category. (2) Count # of products in all categories off a particular branch. I have a separate table that stores the category-product associations. (3) Generate a list of all child categories and their full lineage. This would apply to generating a navigational path on a category page (i.e. Home > Category > Subcat > Child) - it would also apply to generating a list of all children to be placed in a selectable form menu. Does this make sense? Contact me if you have questions. Thanks!