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

3 rows

MessageID 1, 2, 4

Any help would be great.

  • 1
    what do you mean by combined? Id 3 should be remove or id 4 should have text from both id 3 and 4? Can you edit the question and add the expected output? Commented Jan 20, 2016 at 17:06
  • Just made edits, combined was the wrong word. Id 3 should be removed i.e. not present since id 4 is the latest based on date/Id. The expected output will have rows id: 1,2,4 Commented Jan 20, 2016 at 17:44
  • Why is the question tagged with both sql-server and mysql? Which one of the two are you using? Commented Jan 21, 2016 at 10:51
  • Because I wanted to attract more viewership for the query in question whether its SQLServer or MySQL doesn't matter. Whichever way the query can be created I can translate to fit the environment's needs. But it is in fact for a SQLServer db. Commented Jan 21, 2016 at 16:49

1 Answer 1


Why not use this query (sqlfiddle):

SELECT [MessageID]
    , [Body]
    , [Date]
    , [MessageParentID] 
FROM Messages 
        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
  • Thank your sir! Group BY is needed so that if a message is sent to more than one person it doesn't appear twice in the query. I don't necessarily need the Max(M.Date) you're correct. MessageRecipients is needed as it's my intent to query all Messages sent & received by a user at any given time. Here's an updated version of its intended usage: sqlfiddle.com/#!6/6421e3/26. You're solution got me on the correct path thanks again. Commented Jan 21, 2016 at 19:27
  • Your GROUP BY is strictly similar to the selected columns with no aggregate. This is not needed and you can replace it by a simple SELECT DISTNCT... Commented Jan 22, 2016 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.