MySQL statement combining a join and a count?

Question

I've got a table of 'folders'. I want to return all the records with the userId of 16.

SELECT * FROM `folders` WHERE userId = 16;

I've got a table of 'files'. For each 'folder' returned above, I want to return a count of 'files' within that 'folder'.

SELECT COUNT(*) as "Files" FROM files WHERE Folder = n;

How do I combine these? I'm lost. Thanks!

Answer 1

you would probably need to use GROUP BY and group it by ID or such:

SELECT 
    folders.*,
    COUNT(files.*) as filetotal 
FROM folders 
    LEFT JOIN files ON folders.ID=files.folderID 
WHERE userId = 16 
GROUP BY folders.ID

Answer 2

is the userid in the files table? can you groupby the userid and then apply the count to each user?

Answer 3

Do a sub query that groups by the Folders to get the count per folder, then join it to the first query like this:

    select
       f.*
       fc.Files
    from
       Folders f
--
       -- Join the sub query with the counts by folder     
       JOIN (select
               Folder,
               count(*) Files
             from
                files
             group by
                Folder ) as fc
          on ( fc.Folder = f.Folder )
    where
       f.userid = 16

Ron

Answer 4

SELECT  fol.*
 ,      (       SELECT  COUNT(*)
                FROM    files           fil
                WHERE   fil.Folder      = fol.Folder
        )       AS      "Files"
FROM    folders         fol
WHERE   fol.userId      = 16

It's called a correlated subquery.

http://dev.mysql.com/doc/refman/5.1/en/correlated-subqueries.html