Counting Sub Categories

[louissto56]

Hello. I have a recursion type application and need to count the category database including subcategorys.

It is setup like this:

catID - PK
catName - varchar
parentID - integer

For example:
Accomodation (catID = 1) is a top level category (parentID = 0).
Shared Houses is a sub category of accomodation (parentID = 1)
Hotels is a sub category of accomodation (parentID = 1)

Say I have 2 records in shared houses and 1 in hotels, how would I count it so Accomodation has a count value of 3?

Thanks
Louis

 

[r937]

depends

do you want to count all the subcategories, no matter how many levels down?

you'll need to write recursive application logic

do you want to count all the subcategories but only one level down?

that's easy

select top.catName
     , count(down1.catID) as subcategories
  from categories as top
left outer
  join categories as down1
    on down1.parentID = top.catID
 where top.parentID = 0
group
    by top.catName

r937.com | rudy.ca

 

[louissto56]

Hey. Thanks for your help. I wanted to count no matter how many levels down.

Thanks.

 

[louissto56]

OH sorry I forgot to mention. I wanted to count items in another table called "items". It has a column called "catID" and is equal to any given category in the category table.