Hi all. Have been trying to get my head around a problem in my custom messaging system. This is my msg table:
id (PK)
datetime_posted (datetime)
from_id (FK for user id)
to_id (FK for user id)
title (the title obviously)
msg (the body)
status (0 = inbox, 1 - removed)
read_status (0 = unread, 1 = read)
The system currently allows me to fwd, reply, remove and send new msgs. It also sends msgs to multiple recipients (no cc or bcc req'd). So if I send a msg to 3 users, 3 new records appear in the table (identical) except for the to_id field.
I have a ajaxified page that allows 'inbox' and 'sent' views. The inbox is fine, but the sent view shows every msg sent, so 3 msgs will show in response to a single msg to 3 recipients.
see screenshot (sorry in Welsh, but you'll get the idea)
I'm looking to create a html table with the following structure:
datetime
title
recipeints with 'read_status' following their names
Something like this: screenshot2
I've fiddled with GROUP BY and subqueries, but they look a bit bloated and take a while to run.
I'd be grateful for some SQL advice. Thanks.