specific mysql select query

Question

today i need your help to get an specific sql select query.

i have following table:

and after a specific query regarding a specific id (in this case id 1) i wanna have a result like this:

user_id (an alias for the id_sender/id_recipient), date (maybe a max function, cause i wanna have the latest date to group), messages (a count function to the messages):

10 | 2012-01-14 09:10:05 | 4
11 | 2012-01-13 13:52:49 | 1
13 | 2012-01-13 20:01:17 | 1
14 | 2012-01-14 09:20:17 | 1

i tryed a lot but dont get the exact results - so my approach was something like this:

SELECT `id_recipient`, `id_sender`, MAX(`date`) AS `date`, COUNT(*) AS `messages` FROM `table` WHERE `id_recipient` = 1 OR `id_sender` = 1 GROUP BY `id_recipient`, `id_sender`

but then i get this result:

its not so bad but as u can see the 4th line should be included in the results of the first one. i hope u got me. feel free to ask if smth is not clear.

thanks in advance, greetings

Answer 1

Ok, so since we know the value for id_recipient, we can use some math to trick SQL into getting this nasty query done.

Let n be the id value of the person of interest.

We know that the pairing of id_recipient and id_sender will ALWAYS include the user with id value n. Based on the where clause.

Therefore, id_recipient + id_sender == n + id_otherPerson is true.

The resulting query will be very similar to this. (It's been a while, but I don't think I have any syntax problems)

SELECT (`id_recipient` + `id_sender` - n) AS `id_otherPerson`, 
        MAX(`date`) AS `date`, COUNT(*) AS `messages` 
FROM `table` 
WHERE `id_recipient` = n XOR `id_sender` = n 
GROUP BY `id_otherPerson`;

Edit: I've changed it to an XOR, so if person n messages person n, it won't cause all values to be incremented by the number of times n has messaged themself.