SQL Query for Internal Messaging System 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:

Messages

_MessageID (PK) 
_MessageParentID (if a reply this will equal MessageID) 
_Body
_Date
_CreatorID (FK userID of the message creator)


MessageRecipients

_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:

3 rows

MessageID 1, 2, 4

Any help would be great.
Why not use this query (sqlfiddle):

SELECT [MessageID]
    , [Body]
    , [Date]
    , [MessageParentID] 
FROM Messages 
WHERE MessageID NOT IN (
        SELECT MessageParentID FROM Messages
    )


Basicaly, if a MessageID is also a MessageParentID it means there is at least 1 reply. Therefore it only looks for message without replies (ie. without MessageParentID).

With this limited sample, it gives the correct output: 

MessageID   Body                            Date                        MessageParentID
1           Its getting hot in hure         December, 27 2015 00:00:00  0 
2           Where is all the chicken?       December, 28 2015 00:00:00  0 
4           Sometimes its a little hotter   January, 05 2016 00:00:00   3 


I don't unsderstand why:


are you using a GROUP BY with all the columns in SELECT. Do you need a DISTINCT?
are you using Max(M.Date) and anyway, it is also included in the group by
you are joining to MessageRecipients since anythin from the recipient list or count are not needed