How to remove duplicate results?

Question

I'm coding a message system using mysql. Everything works fine when I list users whom I'm conversing with, until I want to add date of the last or the start of conversation. When I add a.date I get duplicate results when the date isnt the same.

Here is my sqlfiddle

Answer 1

Since, you were pulling only user_id then in both cases (send/recieve) it was giving you distinct record. But now with date it is no more distinct. you need to do something like:

SELECT temp.id_user, MAX(temp.date) as date
FROM 
(
SELECT users.id_user,
       a.date
FROM   users
       LEFT JOIN message AS a
              ON users.id_user = a.id_user_recipient
       LEFT JOIN message AS b
              ON a.id_user_recipient = b.id_user_sender
WHERE  a.id_user_sender = 1
UNION DISTINCT
SELECT users.id_user,
       a.date
FROM   users
       LEFT JOIN message AS a
              ON users.id_user = a.id_user_sender
       LEFT JOIN message AS b
              ON a.id_user_sender = b.id_user_recipient
WHERE  a.id_user_recipient = 1  
) as temp 
GROUP BY temp.id_user;

Grabbing max(date) will ensure to return only one record as with group by