Records can't be queried within a given timeframe

[NorthStarDA]

Hi,

I have a table of contacts and a table of contact history. I need to build a query to select only records where it has been at least 2 days (for example) since the last contact. If the record in question has no record in the history table (first contact), it should be returned by default.

-----------
Contacts
-----------
Contact ID

----------
ContactHistory
---------
ContactID
contactDate

How would one go about this, it seems like I could write a sub-query easy enough, but what about if the record doesn't exist in the history table?


=========================================
I have not failed. I've just found 10,000 ways that won't work.
Thomas A. Edison

 

[TonyGroves]

How about:
[tt]
SELECT c.contactid,MAX(contactdate) latestcontact
FROM
contacts c
LEFT JOIN contacthistory h USING (contactid)
GROUP BY contactid
HAVING
latestcontact < curdate() - INTERVAL 2 DAY
OR latestcontact IS NULL
[/tt]

 

[NorthStarDA]

I believe that will work for me, thanks!


=========================================
I have not failed. I've just found 10,000 ways that won't work.
Thomas A. Edison