Unified list of distinct values from two columns of a table-CodePudding

I have a mariadb table called chat_messages. It contains a list of messages that users have sent. This table contains 2 columns containing the sender id and receiver id. I want to generate a unique list of users a certain person has communicated with. I have tried a lot of queries and joins and none of them are working.

If I use SELECT sender_id, receiver_id FROM chat_message WHERE sender_id=1 OR receiver_id=1 GROUP BY sender_id, receiver_id then I get a double list (Users appearing more then once in the list namely as receiver and sender). I need a unique list of user combinations.

What sql query could I use to get a list of unique users some one has been communicating with?

CodePudding user response：

Use UNION DISTINCT with Column Alias

SELECT receiver_id AS ContactedPerson
FROM chat_message 
WHERE sender_id = 1
UNION DISTINCT 
SELECT sender_id  AS ContactedPerson
FROM chat_message 
WHERE receiver_id = 1

CodePudding user response：

In case user=1 (sender) sent a message to user=19 (receiver), and user=19 (sender) replied to user=1 (receiver) you will have two records, which can't be filtered with GROUP BY since both users are sender and receiver.

Records
1 | 19 | Hello
19|  1 | Hi! What's up

Since you want to know only which users talked together, but not who was the sender or receiver of a message, just move the user id with the lower number to the left, the user id with the higher number to the right:

SELECT if(sender_id < receiver_id, sender_id, receiver_id) as person1, 
       if (sender_id < receiver_id, receiver_id, sender_id) as person2
  FROM chat_message WHERE sender_id=1 OR receiver_id=1 
  GROUP BY person1, person2

You can of course also use UNION where the first select puts sender on the left, the seconds puts receiver on the left:

SELECT sender_id, receiver from chat_message where sender_id=1
union select receiver_id, sender_id from  hat_message where receiver=1

According to EXPLAIN the latter one will be slower.