CODEWITHSUNDEEP
mysql
Ivo
Ivo

Reputation: 363

Select where column1 or column2 equal to id, but group by other value of the row

I have this table for messages between users

id |sender |receiver 
--------------------
   |1      |2
   |3      |1
   |1      |4
   |2      |1

I want to select all rows where t.sender OR t.receiver is equal to 1 (user for which i am searching messages), but to group by other values of the row (Group by other users).

In this example result for user1 should contain 3 rows grouped by user2, user3 and user4 values.

Upvotes: 1

Views: 745

Answers (1)

RP-
RP-

Reputation: 5837

One way is to fetch them using union all.First you need fetch messages sent by the user and union those results with the messages received by the same user.

select receiver, count(1), 'Sending' from messages where sender = 1 group by receiver
union all
select sender, count(1), 'Receiving' from messages where receiver = 1 group by sender

Upvotes: 1

Related Questions