Paul J Stevens wrote: > Just to be clear; If you do have an elegant, readable and > maintainable solution I'm all for it.
Hmm...well, the following query might not qualify but it certainly does the trick and removes the need for an internal array. We still have to do a sum of all the results but we're already performing internal arithmetic anyway so I don't see that as a problem. The query is: SELECT t1.flgs, CASE WHEN cnt IS NULL THEN 0 ELSE cnt END AS c FROM (SELECT 0 AS flgs UNION ALL SELECT 1 AS flgs UNION ALL SELECT 2 AS flgs UNION ALL SELECT 3 AS flgs) t1 LEFT JOIN (SELECT seen_flag * 2 + recent_flag AS flags, count(*) AS cnt FROM dbmail_messages WHERE mailbox_idnr = '14' GROUP BY seen_flag * 2 + recent_flag) t2 ON (t1.flgs = t2.flags); Note that the original, simple query is the second query in the LEFT JOIN. The above is as efficient as the simple query itself. The extra stuff is to force the query to return all possible flag combinations regardless of whether or not there are any rows that match. And of course the CASE statement is needed because 0 and NULL are not the same thing, despite what MySQL would tell you. The above works in MySQL versions 4.1 and greater (might work with earlier versions, but I don't know and can't test it without going through a *lot* of trouble), SQLite version 2.8.16, and PostgreSQL 7.4 and later (probably will work for all 7.x and later, actually). I'll create a patch against 2.0.7 for this if you guys are interested. -- Kevin Brown [EMAIL PROTECTED]
