tricky join question

I have the following tables built up!

Folders
folderid | parent | name
0 | 0 | root folder
1 | 0 | myfiles
2 | 1 | myfiles_sub1
3 | 1 | myfiles_sub1

Files
id |folderid | name
1 | 0 | file1
2 | 1 | file2
3 | 2 | file3
4 | 2 | file4
5 | 3 | file5

Now I want to count the files in folder X.

result:
folder->id=0 => 5 files
folder->id=1 => 4 files
folder->id=2 => 2 files
folder->id=3 => 1 file

How do I go about doing that with MYSQL statements. I do not wish to use a while loop or anything like that, preferrably a count/join query.

Much appreciated!!

The code below should be of some help.

SELECT 'In folder ', fo.name, ' there are ', count(fi.id), 'file(s).'
FROM folders fo
INNER JOIN files AS fi ON fo.folderid = fi.folderid
GROUP BY fi.folderid
ORDER BY fo.name

Hi as I explained in the title, tricky join. I need to count the files in the subdirs as well.

Hence the folder myfiles (folderid=1) should have 4 files but the query

SELECT 'In folder ', fo.name, ' there are ', count(fi.id), 'file(s).'
FROM folders fo
INNER JOIN files AS fi ON fo.folderid = fi.folderid
GROUP BY fi.folderid
ORDER BY fo.name

will only count the one level not sublevel/s

So how do I solve that .....

/R