在单个查询中获取文件夹名称和总读取/未读取

Question

My SQL database structure is as follows...

email_folders

id, name

email_messages

id, id_folder, date, date_read

In a single MySQL query I'm trying to get the folder names, their total email messages count both email total count and unread count per folder.

Thus far I have the basic counting bit down though the returned array in PHP returns the number of rows that there are emails for and undesirably does not return all the folders from the folders from the email_folders table.

Here is what I currently have...

SELECT 
ef.name, 
(SELECT count(id) FROM email_messages WHERE id_folder=ef.id AND date_read<date) AS unread, 
(SELECT count(id) FROM email_messages WHERE id_folder=ef.id) AS total 
FROM email_messages AS em
LEFT JOIN email_folders AS ef ON ef.id=em.id_folder;

There are currently four folders though over a hundred rows are returned so I know I'm doing something wrong. I'll be happy to make any necessary clarifications.

Answer 1

select
    f.name FolderName,
    count(m.id) TotalMessagesCount,
    sum(m.date_read < m.date) TotalUnreadMessagesCount
from email_folders f
left join email_messages m on f.id = m.id_folder
group by f.id

I really wonder why date_read < date means Unread for you. Having the date_read set to null makes more sense to me be considered as Unread.