Take the 2-minute tour ×

I got a problem I could not solve.. We got a messages table in our my SQL database with some fields like text, senderid, recipientid, date, ...

A new feature is to group these messages like Facebook in one. Let's say:

  1. user A is sending user B a message
  2. user B is replying to A
  3. User A is sending user C a Message
  4. user D is sending user A a message

So user A must have three messages in his box: B, C, D. B includes (1,2), C includes (3) and D includes (4), even if C is not replying to A (3) and A not to D (4).

A simple group by (senderid) or group by (recipientid) is not enough..

How's that possible?

share|improve this question
Wouldn't 'A' have all four messages? –  vansimke Apr 3 '12 at 13:04
he should have 3 messages in his messages box but message (2) is in the message (1), if you click this to read (like facebook). a timeline of all messages with user A <=> X –  ahmet2106 Apr 3 '12 at 13:31

2 Answers 2

UNION the results:

select boxuser, count(*) from
(select senderid boxuser from messages
 union all
 select recipientid boxuser from messages)
group by boxuser 
share|improve this answer

Not entirely sure what you are looking to achieve, but on the listing pages:

SQL query which selects distinct users where either receipientid or senderid = LOGGEDINUSER

This will list all unique users the logged in user has been in communication with.

The in the messages page for user X:

SQL query which selects all rows where either (receipientid = X and senderid = LOGGEDINUSER) or (senderid = X and receipientid = LOGGEDINUSER)

This will list all message correspondence between the two users.

share|improve this answer

Your Answer


