CODEWITHSUNDEEP
mysqlselect
user1104419
user1104419

Reputation: 77

specific mysql select query

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

Upvotes: 3

Views: 142

Answers (3)

Fahim Parkar
Fahim Parkar

Reputation: 31637

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

Upvotes: 0

JustinDanielson
JustinDanielson

Reputation: 3185

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.

Upvotes: 7

Seramme
Seramme

Reputation: 1350

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.

Upvotes: 0

Related Questions