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!

Sorry - I had to change the parameters a bit - ad decidedto start a new project:

Kemahiran: ASP, XML

Lihat lebih lanjut: to advanced, table data structure, structure stores, questions of data structure, placed, new data structure, need of data structure, list of data structure, list in data structure, list data structure, data structure with c, data structure that, data structure questions, data structure operations, data structure list, c using data structure, on sql, developing a new product, xml to my sql, sql, SQL Query, sql data, sql c, sense, perform

Tentang Majikan:
( 22 ulasan ) Carmel, United States

ID Projek: #8481