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.