SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting records for today - Part 2

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you will need a couple LEFT OUTER JOINs

    Code:
      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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you will need a couple LEFT OUTER JOINs

    Code:
      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):
    Code MySQL:
    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.

  4. #4
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would this be right, taking into consideration the reply must also be in the same day - and after the original mail was sent?
    Code MySQL:
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by LuckyB View Post
    Would this be right...?
    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But need the reply to be after the sent obviously. So if the recipient messaged them earlier in the day it shouldnt be counted.

  8. #8
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  9. #9
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting..Removing the first outer join (mto) SEEMS to have possible fixed the problem:
    Code MySQL:
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •