Self referential table and recursive SQL function

Question

I have a category table with:

• categoryID
• parentCategoryID
• categoryName

and an items table with:

• itemID
• categoryID
• itemName

I am using MySQL. i want to write a query that will return a count of Items in a category given a categoryID. The query should return total count for all items in all subcategories of the given category.

I hope this makes sense.. sorry if i am not using the correct nomenclature.

Answer 1

How stuck are you on that schema? It's called an "Adjacency List", and it's simple enough, conceptually, but it has some real shortcomings. The most significant of which is the inability to query for all descendants.

Take a look at this, and consider whether an alternative method of representing trees might work better for you:

http://pugs.postgresql.org/files/ModelingTrees.pdf