I have a table which looks something like this and sometimes contains 1000s of rows -
cat_id cat_parent_id product_code
1 0 p1
2 0 p2
1 0 p3
3 2 p4
3 2 p5
4 3 p6
4 3 p7
It represents a hierarchy of categories and the products within each category.
Categories at the root of the tree have a cat_parent_id of 0, categories lower down in the hierarchy have a cat_parent_id of their container category.
Within each category there are a number of products, and product_code is unique within the table.
I need to be able to count the number of products within a particular subtree,
subtree(4) contains 2 products.
subtree(2) contains 5 products, includes products in categories 2,3 and 4.
subtree(0) would contain all products.
Currently I am using a very inefficient query and php to find the desired result. I was wondering if an expert could show me a way to do the same using a recurive SQL query on the table ?
Thanks, Chris Coleman.