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 to share