Selecting Number Of Products That Comes Under A Parent Category

  selecting number of products that comes under a parent  category

sqlserver2005

CREATE TABLE [ProductCategoryAssociation](
[CategoryID] [bigint] , --this is primary key of table Category
[ProductID] [bigint] ,--this is primary key of table product
)

 

the above table binds a product to its category

 

CEATE TABLE [Category](
[CategoryID] [bigint] IDENTITY(1,1) -- this is primary key of table category

[Name] [nvarchar](255)

[ParentCategory] [bigint] NULL,
)

 

I have a  Category"A" as a supper parentcategory

I have 1product in   Category"A"

Category"A" have 2 child category A11,A12.

  A11,A12 both have 2 products in the category (totally 4)

Now I  can get the count of all product belong  Category"A"  (ie is 5= 1+2*2) five

let 1 be the id of category "A"

 Now the following querry give me the expected result

select count(ProductId) from ProductCategoryAssociation where  categoryid=1 or   categoryid in (select categoryid  from Category where Parentcategory=1)

 

BUT if i have child for  A11 named A111 and if there is ONE product belong to A111 this querry wont take that in to cosideration

so

the following querry will do it

select count(ProductId) from ProductCategoryAssociation where  categoryid=1 or   categoryid in (select categoryid  from Category where Parentcategory=1 or Parentcategory in (select categoryid  from Category where Parentcategory=1))

ie i nested one more select

buthis has limitations

So kindly give me a general solution to  check up to the las level of category