Selecting records for today - Part 2

[LuckyB]

This is in reference to my original post, which has been moved to PHP - I didn't realise until too late:
http://www.sitepoint.com/forums/showthread.php?t=648498
However this part is MySQL purely and NOT php.
********************************
ok guys. next part to the question, which is purely SQL not php related.

I want to count messages from user 8 where the recipient of the messages is unique between these date ranges - to see how many unique people they contacted.

2nd half- I would further like to refine the above, to only count the times the unque person replied - but this part is over my head.
Eg.
if "user id: 1" contacts "user id: 2" 10 times and "user id: 2" replies ( once or more ) = 1 successful contact attempt

if "user id: 1" contacts "user id: 3" 20 times (or however many) and "user id: 3" doesnt reply = failed contact attempt

the table format is:
id (unique auto increment), from (user id of sender), to (user id of recipient), sent (time as int(10))

each message = 1 record
so if user 1 contacts user 2 = 1 record (from = 1, to = 2)
and user 2 replies to user 1 = 1 more record (from = 2, to = 1)
and user 2 messages user 3 = another record (from = 2, to = 3)

I really hope this makes sense.

So far I got:
SELECT count(DISTINCT messages.to) FROM messages WHERE messages.from = 8 and messages.sent >= 1258927200 and messages.sent <= 1259013599
But that only counts unique attempts (successful and failed contact attempts), not just successful attempts.

[r937]

you will need a couple LEFT OUTER JOINs

  FROM messages AS mfrom
LEFT OUTER
  JOIN messages AS mto
    ON mto.from = mfrom.to
LEFT OUTER
  JOIN messages AS mreply
    ON mreply.from = mto.to
   AND mreply.to = mfrom.from

you would use WHERE mfrom.from = 8 to indicate the user who initiated the contact

you also need a GROUP BY, because you want to count the number of messages and/or contact attempts

and of course you'll want to work that date range test in there, although it's not clear to me whether it should apply to the contact attempt or the reply or both

but the main thing is, you have to understand the two LEFT OUTER JOINs there...

[LuckyB]

you will need a couple LEFT OUTER JOINs

  FROM messages AS mfrom
LEFT OUTER
  JOIN messages AS mto
    ON mto.from = mfrom.to
LEFT OUTER
  JOIN messages AS mreply
    ON mreply.from = mto.to
   AND mreply.to = mfrom.from

you would use WHERE mfrom.from = 8 to indicate the user who initiated the contact

you also need a GROUP BY, because you want to count the number of messages and/or contact attempts

and of course you'll want to work that date range test in there, although it's not clear to me whether it should apply to the contact attempt or the reply or both

but the main thing is, you have to understand the two LEFT OUTER JOINs there...

Hmm I think I understand the joins (though they are my weak point).


Is this what you were meaning (query in full):

SELECT  *  FROM messages AS mfrom
LEFT OUTER
  JOIN messages AS mto
    ON mto.from = mfrom.to
LEFT OUTER
  JOIN messages  AS mreply
    ON mreply.from = mto.to
   AND mreply.to = mfrom.from 
WHERE mfrom.from = 8 and mfrom.sent >= 1258927200 and mfrom.sent <= 1259013599
GROUP BY mfrom.to

Also the replies mreply need to be within the above specified date range too (ie not include any previous contact that may have occurred)

so if user 10 messages user 8 yesterday.
user 8 messages user 10 today, and user 10 DOESNT message user 8 today = not successful contact for user 8 within today.

[LuckyB]

Would this be right, taking into consideration the reply must also be in the same day - and after the original mail was sent?

SELECT  *  FROM messages AS mfrom
LEFT OUTER
  JOIN messages AS mto
    ON mto.FROM = mfrom.to
LEFT OUTER
  JOIN messages   AS mreply
    ON mreply.FROM = mto.to
   AND mreply.to = mfrom.FROM 
WHERE mfrom.FROM = 8 
AND mfrom.sent >= 1258927200 AND mfrom.sent <= 1259013599
AND mreply.sent > mfrom.sent AND mreply.sent <= 1259013599
GROUP BY mfrom.to

[r937]

Would this be right...?

what happened when you tested it?

[LuckyB]

No results (0 records), until I delete "AND mreply.sent > mfrom.sent AND mreply.sent <= 1259013599". If I delete this I get a result, but even if the "reply" was from an earlier period, which is wrong.

[LuckyB]

But need the reply to be after the sent obviously. So if the recipient messaged them earlier in the day it shouldnt be counted.

[LuckyB]

id from to message sent
152 8 17 hello 1259037014
153 17 8 hi back 1259037050

In the perspective of "WHERE mfrom.FROM = 8" should be 1 (because the message from 17 was after (but in the same day) as the message from 8)
In the perspective of "WHERE mfrom.FROM = 17" should be 0 (because the message from 8 was before the message from 17)

[LuckyB]

Interesting..Removing the first outer join (mto) SEEMS to have possible fixed the problem:

SELECT * FROM messages AS mfrom 
LEFT OUTER JOIN messages  AS mreply
  ON mreply.FROM = mfrom.to 
  AND mreply.to = mfrom.FROM 
WHERE mfrom.FROM = 8 AND
  mfrom.sent >= 1259013600 AND mfrom.sent <= 1259099999
  AND mreply.sent > mfrom.sent AND mreply.sent <= 1259099999 
GROUP BY mfrom.to

Would love your opinion r937.

Thanks

[r937]

yeah, that looks like it'll work

i'm not sure why i had two joins

of course, i was coding blind, i didn't have any data...