Self Referencing Table Query

[rico14d]

Hi,

The Scenario:
I am building a product database, each product belongs to a category. Each category will (usually) belong to a parent category. I need a query which can return each category and the number of items in it
e.g.

Computing(320)
Laptops(100)
PCs(100)
Printers(120)
Dot-matrix(50)
Laser(40)
Ink Jet(10)
Accessories(20)
Paper(5)
Ink(15)
Hi-fi

tblCategory(CategoryID, ParentCategoryID, CategoryDesc)
tblItem(ItemID, CategoryID, ItemDesc)

At present i have a recursive function in my code but this list could become very large so id rather use 1 neat little query!

Anyone have any ideas?

Rich

 

[FALCONSEYE]

select tcl.CategoryDesc, count(tbi.itemID) as productsInThisCat
from tblCategory tcl, tblItem, tbi
where tcl.CategoryID = tbi.CategoryID
group by tcl.CategoryDesc

hope it helps