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