Determining if all messages are read within a thread MySQL

Lets say you had a database schema that looked like this:

Message: (id: int PK, read: boolean, thread_id: int FK, ...)

Thread: (thread_id: int PK, ...)

in Message the read value represents if both parties have viewed the message.

How would you construct a query that returns every thread_id, and another value that represents if every message belonging to the thread has been read. EG: Messages: (1, 1, 1), (2, 1, 1), (3, 0, 1), (4, 1, 2), (5, 1, 2), (6, 1, 2)

Querying this would return as (read, thread_ID) => (0, 1), (1, 2) Thread 1 has a read value of 0 because a single message was unread. Thread 2 has a read value of 1 because every message was read.

How would you make this query?

Answer

select thread_id, min(`read`) as `read` from message group by thread_id

Leave a Reply

Your email address will not be published. Required fields are marked *