MySQL Inbox query returning double results

Question

Using PHP and MySQL, I'm creating an inbox messaging system and it works, but it is returning doubles in a sense. The inbox should display a conversation between two people with the last message that was sent. The user can click on that latest message and chat with the user. Right now the inbox is showing the latest message sent by user 1 if user 1 messages user 2, AND it is showing the latest message if user 2 messaged user 1. If user 1 OR user 2 message each other, I just want to show the LAST message sent by either of them. Here is my query, I wanted to do this on my own, but my SQL skills have dulled a bit.

Here is my message table:

Here is my user table:

Lastly, here is the query:

SELECT m.body, m.createddate,m.recuserid,m.createduserid,
    FROM( SELECT MAX(createddate) as maxdate 
     from 

     messages 

     group by recuserid,createduserid
    ) c 
inner join messages m on m.createddate = c.maxdate 
inner join users u on u.userid = m.createduserid 

WHERE createduserid = 143 OR recuserid = 143

And here is the results that it returns:

As you can see, it returns any message that involves user 143 in any way. My desired results are to only return the 1st and 3rd row instead.

Answer 1

I think this is what you're after:

SELECT m.`body`,
       MAX(m.`createddate`) AS maxcreateddate,
       m.`pairid`
FROM (SELECT `body`,
             `createddate`,
             CONCAT(`recuserid`,`createduserid`) as pairid
      FROM `messages`
      WHERE `recuserid`=143
    UNION
      SELECT `body`,
             `createddate`,
             CONCAT(`createduserid`,`recuserid`) as pairid
      FROM `messages`
      WHERE `createduserid`=143) as m
GROUP BY pairid;

Someone might roast me about performance, but it does give you the result you're looking for (just the first and third rows).

Hope it helps ;)