SQL Query for Internal Messaging System

Question

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.

• Julien Vavasseur: 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?
• sacbemeezer: 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
• ypercubeᵀᴹ: Why is the question tagged with both sql-server and mysql? Which one of the two are you using?
• sacbemeezer: 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.

Answer 1

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

• sacbemeezer: 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.
• Julien Vavasseur: 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...