help with nested SQL

[mfacer]

I'm not sure how to do this... but I have a structure like this

Category ID
Category Name
Category Parent
No. of files

eg: Category name = maths
catid = 1
parentid=0
files = 3

category name = handouts
catid=2
parentid = 1
files = 1

so the main category is maths, with a sub category of "handouts". Each category has files listed in it...
I need a way of getting the main category (parent) then looking at each of it's children categories and counting the number of files in each.

do I need a nested query??
thanks,
matt.

[liquorvicar]

i believe you can join the table to itself thus:

SELECT t1.catid, sum( t2.files )
FROM table AS t1, table AS t2
WHERE t1.catid = t2.parentid
GROUP BY t1.catid