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

Databases
1

Given the following tables:


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

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.

3

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

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().

5

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