August 25, 2013
Single MySQL query to fetch folder names, email count per folder
John’s Question:
I have the following two MySQL tables for emails…
mail_emails
Columns: id, folder, user
mail_folders
Columns: id, name, user
I want to use only a single MySQL query to fetch the folder names and the number of emails per folder. Presuming the user id is 1, what would be the most efficient approach to this goal?
I am not exactly sure of your database structure. But this should give you an idea.
SELECT *, count(e.folder) totalMail FROM mail_folders f INNER JOIN mail_emails e on e.folder = f.name WHERE f.user = '1' GROUP BY e.folder
I am assuming that the name field in the mail_folders table is the same value that will be stored in folder field of mail_emails table.
