mysql count(*) left join group by - the number of files in a folder

Question

Hi

I have the following tables

CREATE TABLE `files` (
  `fileid` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `filesize` int(11) NOT NULL,
  `folder` int(11) NOT NULL,
  PRIMARY KEY (`fileid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE TABLE `folders` (
  `directoryid` int(11) NOT NULL AUTO_INCREMENT,
  `directoryname` varchar(255) NOT NULL,
  PRIMARY KEY (`directoryid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

How to get a list of all folders and the number of files they hold, including folders who have no (zero) files? Recursion must not be taken into account.

Found it

select folders.directoryid, folders.directoryname, count(files.fileid) as no_files
from  folders
left join files on files.folder = folders.directoryid
group by folders.directoryid, folders.directoryname

I hope it will help someone.

Answer 1

You could try something like

SELECT f.directoryid,
   COUNT(fs.fileid) CountOfFiles
FROM folders f LEFT JOIN
    files fs ON f.DirectoryID = fs.folder
GROUP BY f.directoryid

Answer 2

SELECT directoryname, COUNT(files.fileid) AS filecount
FROM folders
LEFT JOIN files ON files.folder = folders.directoryid
GROUP BY directoryid
ORDER BY directoryname

Answer 3

select folders.directoryname, 
       count(files.fileid) 
from folders left outer join 
     files on folders.directoryid = files.folder 
group by folders.directoryid

That should do it