SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    return most recent that i have not already seen

    hi all

    basically i want to merge these two queries
    Code:
     //get the user's id
     SELECT user_ID, user_Nick FROM urban_userlog ORDER BY user_LoggedInDate DESC, user_LoggedInTime DESC
     
     //get the id of a user that i have not voted for in the past hour
     SELECT uservotelog_VoteeUserID FROM urban_uservotelog WHERE
     uservotelog_VoterUserID <> '1' AND UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(uservotelog_DateTime) < 3600
    the query below, eg. returns 100 records
    with the timestamp part taken out, it returns eg. 1000 records
    even though i may have voted for only 20 users
    so the query below, should return eg. 980 records

    all the relavant fields are there, table name is what is before the underscore
    votelog contains all vote details, userlog contains user details

    Code:
        SELECT DISTINCT user_ID, user_Nick FROM urban_userlog 
     		 	LEFT OUTER JOIN urban_uservotelog ON user_ID = uservotelog_VoteeUserID
     		 	WHERE uservotelog_VoterUserID <> '1' AND UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(uservotelog_DateTime) < 3600 
     		 	ORDER BY user_LoggedInDate DESC, user_LoggedInTime DESC
    just checked, in fact the query above returns records that i have already seen as well

    when i run a query to select who i have voted for in the past hour or half hour using that timestamp method
    it returns the correct result

    thanks
    dave
    Last edited by dtra; Dec 6, 2004 at 19:23.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select user_ID
         , user_Nick 
      from urban_userlog 
    left outer 
      join urban_uservotelog
        on user_ID 
         = uservotelog_VoteeUserID
       and uservotelog_VoterUserID <> '1' 
       and uservotelog_DateTime
         > date_add(now(), interval 1 hour)
     where uservotelog_VoteeUserID is null     
    order
        by user_LoggedInDate desc
         , user_LoggedInTime desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks as always mate
    if i move this part into the where statement does this affect the query much

    from left outer join to where

    AND uservotelog_VoterUserID <> '1'

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't know

    do an EXPLAIN on it, it might shed some light
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cool, yeah it was other bad queries that were slowing it down


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
  •