Lisa Haase Lisa Haase - 3 months ago 8
SQL Question

Mysql Query combine two colums and get unique values of both

I just can't figure out how to do the following.

What I have

... is a mysql table "mails" like:

id | sender | recipient | date | content | read
---------------------------------------------------------------
1 | 3 | 2 | 2016-07-29 09:04:21 | hello | 1
2 | 2 | 3 | 2016-07-29 09:14:21 | hello | 1
3 | 1 | 2 | 2016-07-29 09:24:21 | hello | 1
4 | 1 | 3 | 2016-07-29 09:34:21 | hello | 0
5 | 1 | 1 | 2016-07-29 09:44:21 | hello | 1
6 | 3 | 1 | 2016-07-29 09:54:21 | hello | 0
7 | 1 | 1 | 2016-07-29 09:56:21 | hello | 1
8 | 1 | 3 | 2016-07-29 09:58:21 | hello | 0
9 | 2 | 2 | 2016-07-29 09:59:21 | hello | 0


What I need

... is to get an overview of the latest chatpartners for one person, like the user with id
1
(the user can also send messages to himself) like

date | partner
2016-07-29 09:58:21 | 3
2016-07-29 09:56:21 | 1
2016-07-29 09:24:21 | 2


No matter if the user is recipient or sender and no matter how many messages have been send or received. (I included the date column in the example output to clarify I need them sorted by date DESC, it does not need to be included in the final result)

What I tried

SELECT DISTINCT sender,recipient
FROM mails
WHERE recipient=1 OR sender=1
ORDER BY date DESC


but of course it does not combine the two columns. Output is

sender | recipient
1 | 3
1 | 1
3 | 1
1 | 2


If I add
GROUP BY sender
it does not display the mails where user 1 is only recipient.

I also tried the following

SELECT
max(date) as d,
CONCAT(greatest(sender,recipient),"_",least(sender,recipient)) AS p
FROM mails
WHERE recipient=1 OR sender=1
GROUP BY p ORDER BY d DESC


which, for example, gives

d | p
2016-07-29 09:58:21 | 3_1
2016-07-29 09:56:21 | 1_1
2016-07-29 09:24:21 | 2_1


What seems to be great, I can only take the first id shown ...
but when I look for user id 2, it gives

d | p
2016-07-29 09:59:21 | 2_2
2016-07-29 09:24:21 | 2_1
2016-07-29 09:14:21 | 3_2


... so it is unclear if the first or second id is the partner's id.

Result

I'm kind of desperate. Any help would be great.

Answer

I think you want an aggregation like this:

select max(date) as date,
       (case when m.recipient = 1 then m.sender else m.recipient end) as partner
from mails
where 1 in (m.recipient, m.sender)
group by (case when m.recipient = 1 then m.sender else m.recipient end)
order by max(date);
Comments