dav dav - 4 years ago 88
MySQL Question

Conditional count with group by in where clause

I have a simple messaging system - keeping all the messages in a single table. Each message can(and should) be associated with one of the 3 other tables, that represent some sections of the website. Here is the create table statement

CREATE TABLE `messages` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`from_user_id` int(11) DEFAULT NULL,
`to_user_id` int(11) DEFAULT NULL,
`message` text COLLATE utf8_bin,
`table1_id` int(11) DEFAULT NULL,
`table2_id` int(11) DEFAULT NULL,
`table3_id` int(11) DEFAULT NULL,
`is_unread` tinyint(1) DEFAULT NULL,
`date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


For each message, from
table1_id, table2_id and table3_id
if some column has value, it means the rest 2 are null. Here is the sqlfiddle structure and example data: http://sqlfiddle.com/#!9/b98a2/1/0.

So,
table1_id, table2_id and table3_id
are sort of threads, that I am using when grouping - to show the list of messages. Here is my query

SELECT
id,
table1_id,
table2_id,
table3_id,
message,
from_user_id,
to_user_id,
COUNT(table1_id) AS t1_count,
COUNT(table2_id) AS t2_count,
COUNT(table3_id) AS t3_count,
MAX(CASE WHEN to_user_id = 10 AND is_unread = 1 THEN 1 END) AS is_unread,
COUNT(CASE WHEN to_user_id = 10 THEN 1 END) AS inbox_count
FROM
messages
WHERE to_user_id = 10 OR from_user_id = 10
GROUP BY table1_id,
table2_id,
table3_id
ORDER BY id DESC


and this is in sqlfiddle http://sqlfiddle.com/#!9/b98a2/2/0

This query works fine when I have to show all the messages, but if e.g. I want to show only inbox of the user with
id = 10
I have to check the condition that for each thread there is at least one received message, so for that I tried to apply the condition
AND inbox_count > 0
, which resulted an error
Unknown column 'inbox_count' in 'where clause'
.

I am trying to list messages similar to gmail - showing the number of total messages(
t1_count, t2_count or t3_count
) per thread, that is why I can not remove the
OR from_user_id = 10
part.

Why it can not find that column and how I can apply that condition to show the list of received(outgoing) only messages.

jpw jpw
Answer Source

Unless I completely misunderstand your intent... If you want to filter with inbox_count > 0 to then I think you want to add a

HAVING COUNT(CASE WHEN to_user_id = 10 THEN 1 END) > 0

after the group by clause. This would remove the "threads" that don't have any message with to_user = 10.

See this fiddle for and example.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download