dharmendra chaudhary dharmendra chaudhary - 4 months ago 16
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

You can use CASE EXPRESSION :

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
Comments