Distinct repeating values from 2 columns

[php_nub_qq]

Hello.

 

I have a messages table which holds all messages that users type ( chat ). What I'm trying to create is a division which holds all conversations of the user currently browsing. Much like facebook, it is supposed to hold every single person that the user has ever chatted with and the last message no matter who the sender is. My table's columns are ID, sender, recipient, message, time and seen. I know this can be done in multiple queries as well as in PHP but I'm trying to go pro on this project and thus I aim to do it in one. I'm close to achieving my goal but I just couldn't struggle anymore and I'm posting for some help

 

This is my query

SELECT `message`, `recipient`, `sender`, (SELECT `username` FROM `users` WHERE `id`=`recipient`) AS 'recipientName', (SELECT `username` FROM `users` WHERE `id`=`sender`) AS 'senderName', time, seen FROM (SELECT * FROM `messages` WHERE `recipient`='{$id}' GROUP BY `sender` UNION SELECT * FROM `messages` WHERE `sender`='{$id}' GROUP BY `recipient`) AS D 

This is really difficult to explain so I'll just give you a table of results

 

the ID for this example is 6

 

 

 

and this is what I'm trying to get to

 

Edited May 1, 2013 by php_nub_qq

[Barand]

try this

SELECT m.message, m.recipient, m.sender
    , r.username as recipient_name
    , s.username as sender_name
    , m.time
    , m.seen
FROM messages m
    INNER JOIN user r ON m.recipient = r.user_id
    INNER JOIN user s ON m.sender = s.user_id
    INNER JOIN 
    (
    SELECT LEAST(recipient, sender) as a
        , GREATEST(recipient, sender) as b
        , MAX(time) as time
    FROM messages
    GROUP BY a,b
    ) as latest
    ON LEAST(m.recipient,m.sender) = latest.a
    AND GREATEST(m.recipient,m.sender) = latest.b
    AND m.time = latest.time
WHERE 6 IN (m.recipient, m.sender)
ORDER BY time DESC;

[php_nub_qq]

There is no way in heavens I could have done that. Big appreciation, works like a charm. I'm now diving into understanding the logic! Thanks again