SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Central Illinois
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select records from one table based on data in a second table

    Given the following tables:

    Code:
    Advertisers
    -----------------------------
    Company    |   Expiration_Date
    -----------------------------
    XYZ Co.    |   1356972959
    ABC Inc.   |   1354291206
    -----------------------------
    
    
    Ads
    -------------------------------------------------
    Company    |   URL                      TEXT
    -------------------------------------------------
    XYZ Co.    |   www.example.com          Click here
    ABC Inc.   |   www.mysite.com           On sale now
    XYZ Co.    |   www.otherexample.com     Widgets here
    -------------------------------------------------
    The idea is to select a single random record from the Ads table for each advertiser whose expiration date is in the future (each advertiser may have several links, but I only want the query to return one for each company). I've tried many things and all seem to bring up a result close to, but not exactly, what I need. The closest I've come is as follows:

    SELECT *
    FROM Ads
    JOIN Advertisers ON Ads.Company = Advertisers.Company
    WHERE Advertisers.Expiration_Date < CURTIME( )

    That particular query selects some records where the advertiser's expiration date is in the past, and is missing at least one where the expiration date is in the future, so obviously I'm getting something wrong.

    Any pointers much appreciated.

    Thanks,

    Matthew

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Well you got your < backwards, for starters. (You want advertisers whose expiry date is in the FUTURE, which would mean the expiry date is GREATER than the current timestamp.

    "Random" is the odd word out here. Difficult to do, though my head's telling me a combination of LIMIT, ROUND and RAND might get you roughly there.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Central Illinois
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had the > correct in the real code, I just reversed it for testing purposes (at which time it returned some results when I didn't think it should) and forgot to update it before pasting here.

    I'll be assigning more criteria later on, including RAND and LIMIT, but I don't see how those would help me with the problem I'm having. For now I want to return the broadest results possible, just to make sure the query is looking for the right records. Once I get that nailed down, I can add some randomness and limits.

    For now, the problem is the query is returning some records where the expiration date is in the past as well as the future, and if I turn the > around to be <, then it returns some records where the expiration date is in the future as well as in the past, but different records in each case. Basically it's all a mess and I can't comprehend what it's doing.

  4. #4
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using 'from_unixtime', those 2 expiration_dates are 2013-01-01 03:55:59 & 2012-12-01 03:00:06.

    curtime() for me right now is 12:40:51.

    Try using now() instead or curtime().
    Lats...

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Lats View Post
    Try using now() instead or curtime().
    no, those two are the same thing

    the expiration dates (e.g. 1356972959) are integers representing unix epoch timestamps

    so to make a comparison to the current datetime, use UNIX_TIMESTAMP() without a paramenter
    r937.com | rudy.ca | 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
  •