Group on multiple columns with inner join

I have the following database.

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

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

      

A message can be sent to multiple recipients and I want a unique list of all recipients and senders. The problem is that the sender can also be the receiver, so I get duplicates.

Example:

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 there are now duplicates. I want one of them, not one where the sender or receiver is unique in any of these areas. For this, I added msg.reciever to the group 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 creating unique results in two columns?

For clarification, the end result should be:

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

      

or

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

      

+5


source to share


1 answer


Use concatenation to create one custom column.

Something like that:



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

      

0


source







All Articles