Results 1 to 3 of 3

Thread: tricky join question

  1. #1
    Join Date
    Jan 2003
    Posts
    3

    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!!

  2. #2
    Join Date
    Jan 2003
    Posts
    2
    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

  3. #3
    Join Date
    Jan 2003
    Posts
    3

    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
  •