specific mysql select query

Question

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

i have following table:

table with all results

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:

my approach

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

JustinDanielson · Accepted Answer · 2012-01-14 09:24:29Z

7

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.

answered Jan 14, 2012 at 9:24

JustinDanielson

3,1851 gold badge21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JustinDanielson Over a year ago

1 + 10 - 1 = 10 and 10 + 1 - 1 = 10

user1104419 Over a year ago

damn, never thought of smth like this - really nice way of dealing with such a problem - i will keep this in mind for similiar problems as well!

user1104419 Over a year ago

yes and damn clean solution (never thought its working without nesting/subqueries and such stuff). thank you.

Seramme · Accepted Answer · 2012-01-14 09:22:55Z

0

What about this?

SELECT user_id, MAX(date), COUNT(*)
FROM (
    SELECT id_recipient AS 'user_id', date
    FROM table
    WHERE id_recipient <> 1 AND id_sender = 1
    UNION
    SELECT id_sender AS 'user_id', date
    FROM table
    WHERE id_recipient = 1 AND id_sender <> 1
) AS tbl
GROUP BY user_id

It assumes you want to use id_recipient if the id_sender is 1 and id_sender if id_recipient is 1.

answered Jan 14, 2012 at 9:22

Seramme

1,3257 silver badges9 bronze badges

Comments

Fahim Parkar · Accepted Answer · 2012-01-14 09:26:17Z

0

I believe the output you want should be as below

 10 | 2012-01-13 20:01:17 | 3
 11 | 2012-01-13 13:52:49 | 1
 13 | 2012-01-13 20:01:17 | 1

I'm saying as you are mixing id_recipient and id_sender

answered Jan 14, 2012 at 9:26

Fahim Parkar

31.8k46 gold badges169 silver badges285 bronze badges

2 Comments

JustinDanielson Over a year ago

He wants to group any communication between 1 and 10. Regardless if 1 is the sender or receiver. 14 should be included since 14 received a message from 1.

user1104419 Over a year ago

justinDanielson is right - i wanna collect all messages between two users not just the sent or received ones.

Collectives™ on Stack Overflow

specific mysql select query

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related