dharmendra chaudhary dharmendra chaudhary - 1 year ago 46
SQL Question

How to use group by 1,2 and 2,1 both

MY query like that

select id,user_id,friend_id
from message
where user_id = '$user_id'
or friend_id = '$user_id'
group by user_id,friend_id;

But i got multiple record for e.g 1,61 and 61,1 users . i want a single record but two user either message sent or received. is it possible set group by user_id,friend_id and friend_id,user_id

Answer Source


SELECT t.id,
       CASE WHEN t.user_id > t.friend_id THEN t.user_ID ELSE t.friend_ID end as ID_1,
       CASE WHEN t.user_id > t.friend_id THEN t.friend_id ELSE t.user_id end as ID_2
FROM message t 
where t.user_id = '$user_id' or
 t.friend_id = '$user_id' 
group by ID_1,ID_2