mysql count(*) left join group by - the number of files in a folder
- by Flavius
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.
