Home How can I can all unique user whom I send message and receive message

# How can I can all unique user whom I send message and receive message

Abhishek Agarwal
1#
Abhishek Agarwal Published in 2018-02-13 01:34:43Z
 I have table in which all conversation is stored like this Suppose I have a table like this (fromUser and toUser is fk from user table) ----------------------------------------- Id. | FromUser | toUser | message | | | | | 1 | 1 | 2 | Hi | 2 | 2 | 1 | hello | 3 | 3 | 1 | hi | 4 | 1 | 4 | hello | -----------------------------------------  Desired output for distinct row for user 1 orderBy last updated date ----------------------------------------- Id. | FromUser | toUser | message | | | | | 2 | 2 | 1 | hello | 3 | 3 | 1 | hi | 4 | 1 | 4 | hello | -----------------------------------------  Desired output for distinct row for user 2 orderBy last date ----------------------------------------- Id. | FromUser | toUser | message | | | | | 2 | 2 | 1 | hello | -----------------------------------------  Desired output for distinct row for user 3 orderBy last date ----------------------------------------- Id. | FromUser | toUser | message | | | | | 3 | 3 | 1 | hi | -----------------------------------------  As well for other users
GaryL
2#
 The query I came up with is a bit complex, so here it is in multiple steps: 1) Get the “Other User” for the message matching the requested user. SET @id = 1; SELECT id, FromUser, ToUser, updateDate, IF(FromUser=@id, ToUser, FromUser) as OtherUser FROM user_msgs WHERE @id=FromUser OR @id=ToUser;  2) Wrap the above and get the last updateDate. (see below for user 'id' instead) SET @id = 1; SELECT OtherUser, max(updateDate) as updateDate FROM ( SELECT id, FromUser, ToUser, updateDate, IF(FromUser=@id, ToUser, FromUser) as OtherUser FROM user_msgs WHERE @id=FromUser OR @id=ToUser ) AS i1 GROUP BY OtherUser;  3) FULL QUERY HERE ... Finally wrap again to get the desired records. SET @id = 1;  SELECT * FROM user_msgs O WHERE EXISTS ( SELECT * FROM (   SELECT OtherUser, min(updateDate) as updateDate FROM (   SELECT id, FromUser, ToUser, updateDate, IF(FromUser=@id, ToUser, FromUser) as OtherUser FROM user_msgs WHERE @id=FromUser OR @id=ToUser   ) AS i1 GROUP BY OtherUser   ) i2 WHERE O.updateDate = i2.updateDate AND (( O.FromUser=@id AND i2.OtherUser = O.ToUser) OR ( O.ToUser=@id AND i2.OtherUser = O.FromUser) ) );  Note: If you don’t have a last date "updateDate" field, and instead are using “id” (I wasn’t sure), then just replace all the “updateDate” field occurrences in the query with “id”. SQL Fiddle: link