Recursive queries help REQ 2

[Sammy145]

I have a 2 tables one that holds categories and sub cats
so the structure is
CategoryTBL --
CatID int,
ParentCatID int ,
CatName varchar(100)

values
1001 | 1002,
1000 |1001,
Cat1 | Cat2

Table 2 is where i hold all the products against a CatID
the structure is
CatID ,
ProductID

what i need to do is for each child category calulate all the products and the higher up you go in the parent id it sums up the count

example say Planes has 3 child categories (each with 20 products) i want the planes category to be the sum of the 3 so 60 can someone show me how to do this

i will be running this procedure one a day
im on sql 2000 but will be going to sql 2005.

 

[Catadmin]

So, if I understand correctly, you have data similar to:

Planes (Parent)
1 Prop (Sub1) 10 products
2 Prop (Sub2) 12 products
Jet (Sub3) 3 products

and you want a result similar to

Parent #ofSubs #ofProducts
Planes 3 25

Correct?

If so, you need to use the COUNT keyword along with joining your subtables to each other and then joining them to the parent table. However, if you have a potentially unlimited number of subcategories, you might consider a CURSOR to go through them all and add on variable counters.

Yes, I know CURSORS are Bad Things<tm>, but there are times where you can't avoid them.

So, the keywords you need to look up in BOL (Books Online) are "JOIN", "COUNT", "GROUP BY" and "CURSOR".



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLSister]

http://www.tek-tips.com/faqs.cfm?fid=5322

Questions about posting. See faq183-874