2

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.

  • 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? – Julien Vavasseur Jan 20 '16 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 – sacbemeezer Jan 20 '16 at 17:44
  • Why is the question tagged with both sql-server and mysql? Which one of the two are you using? – ypercubeᵀᴹ Jan 21 '16 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. – sacbemeezer Jan 21 '16 at 16:49
0

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
  • 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. – sacbemeezer Jan 21 '16 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... – Julien Vavasseur Jan 22 '16 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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