-
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
-
what about the parents?
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|