Creating a dynamic column on grouped mysql query

Question

I have the following table in mysql

msg_id | thread_id | read_status
--------------------------------
1      | 1         | read
2      | 1         | read
3      | 2         | unread
4      | 2         | read
5      | 2         | read
6      | 3         | read
7      | 3         | unread

I want a query that will show me all messages grouped by thread_id and if they contain any unread messages I want the a column called read_status to show unread

so the query result would look like this:

thread_id | read_status
-----------------------
1         | read
2         | unread
3         | unread

So far i have:

SELECT 
thread_id,
IF(user_read_status = 'U',"unread","read") as message_status FROM messages
GROUP BY thread_id

but this populates the "read_status" with the result of whatever the first message is, rather than if any of the messages are unread...

I have no idea about how to do this, can anyone help?

Thanks in advance

Answer 1

You need aggregation, such as:

select thread_id, 
       (case when sum(user_read_status = 'U') > 0 then 'unread' else 'read' end) as thread_status
from messages
group by thread_id;

If the message statuses are really "read" and "unread" (as in the sample data), you can take this shortcut:

select thread_id, max(message_status) as thread_status
from messages
group by thread_id;

This only works because you have two statuses and the alphabetical ordering has a meaning.