I'm creating an internal messaging system (No SMTP/POP3) and I'm looking for a way to query a user's list of messages. Here's my schema:
_MessageID (PK) _MessageParentID (if a reply this will equal MessageID) _Body _Date _CreatorID (FK userID of the message creator)
_MessageRecipientID (PK) _MessageID (FK to message) _SendTo (FK UserID)
In short what I'm doing is creating a one to many relationship for messages. One user can send a message to many recipients. But the replies are one to one, in short message blasts are possible but there are no group conversations.
I created a SQL Fiddle to demo: http://www.sqlfiddle.com/#!6/6421e3/7
In the query I have now, I'm close. The User 101 should have 3 rows instead of the 4 showing.
MessageID 4 is a reply to
MessageID 3 (MessageParentID = MessageID) so I'm wanting the latest message to display and since the date of
MessageID 4 is greater it should be displayed in the query. Think how FB Messaging works, you see the latest reply to a thread - not a separate outbox.
E.g. expected output:
MessageID 1, 2, 4
Any help would be great.