5

I have the following database.

msg
....
 - uid (pk)
 - sender
 - msg

relation
---------
 - uid (fk)
 - reciever

A message can go to several recipients and I want to receive a unique list of all recipients and senders. The problem is that a sender can also be a reciever so I get duplicates.

An example is :

SELECT  msg.sender,relation.reciever,msg.msg,msg.uid from msg inner join relation on msg.uid = relation.uid group by msg.sender;


+-------------+-------------+-------+-----+
| sender      | reciever    | msg   | uid |
+-------------+-------------+-------+-----+
| 123         | 321         | Test1 | 1   |
| 321         | 123         | Test2 | 2   |
+-------------+-------------+-------+-----+

The problem is that now, duplicates exist. I want one of them, not both where sender or reciever is unique in either field. To accomplish this I added msg.reciever in the group by like this :

SELECT  msg.sender,relation.number,msg.msg,msg.uid from msg inner join
 relation on msg.uid = relation.uid group by msg.sender, relations.reciever;

But the result is the same. How would I accomplish the task of producing unique results across two columns?

For clarification the final result should be:

+-------------+-------------+-------+-----+
| sender      | reciever    | msg   | uid |
+-------------+-------------+-------+-----+
| 123         | 321         | Test1 | 1   |
+-------------+-------------+-------+-----+

or

+-------------+-------------+-------+-----+
| sender      | reciever    | msg   | uid |
+-------------+-------------+-------+-----+
| 321         | 123         | Test2 | 2   |
+-------------+-------------+-------+-----+
Hichem BOUSSETTA
  • 1,791
  • 1
  • 21
  • 27
chriskvik
  • 1,281
  • 2
  • 10
  • 32
  • I think this may help you: https://stackoverflow.com/questions/12188027/mysql-select-distinct-multiple-columns – xsami Jun 12 '17 at 19:34
  • Sorry if I'm not on the same page here, but I would want the distinct combination of values? As in the opposite of what he is asking? – chriskvik Jun 12 '17 at 19:41

1 Answers1

0

Use a union to create a single column on which to apply the group by.

Something like this:

select * from 
(
      SELECT  msg.sender as user, msg.msg,msg.uid from msg 
      inner join relation on msg.uid = relation.uid
   union
      SELECT  relation.reciever as user, msg.msg,msg.uid from msg 
      inner join relation on msg.uid = relation.uid
) Temp
group by user
Aurelion
  • 168
  • 7