GoFuckYourself.com - Adult Webmaster Forum - View Single Post

DigitalPimp · 01-20-2009, 08:44 PM

Ok, I have two tables in MySQL and need help constructing a query. First table is to store URLs and a second to log results of testing the URLs. The log table includes a column containing a Unix timestamp of the last test. I am trying to write a single query to return all those URLs that have either a NULL entry in the log table (they have not been tested yet) or their most recently logged test (highest timestamp for each) was greater than X seconds ago. The following don't work but may help give an idea of what I want.

SELECT t1.checkid, t1.checkurl, t1.seconds, t2.checktime as checktime FROM Check_urls as t1 LEFT OUTER JOIN Check_log as t2 on t1.checkid = t2.checkid GROUP BY t1.checkid HAVING (checktime < (DYNAMICALLY INSERTED UNIX TIMESTAMP GETS INSERTED INTO HERE - t1.seconds) AND checktime = max(checktime)) OR checktime IS NULL ORDER BY checktime ASC

01-20-2009, 08:44 PM
DigitalPimp Confirmed User Industry Role: Join Date: Jun 2003 Location: Los Angeles, CA Posts: 512	Can anyone help me with an SQL query? Ok, I have two tables in MySQL and need help constructing a query. First table is to store URLs and a second to log results of testing the URLs. The log table includes a column containing a Unix timestamp of the last test. I am trying to write a single query to return all those URLs that have either a NULL entry in the log table (they have not been tested yet) or their most recently logged test (highest timestamp for each) was greater than X seconds ago. The following don't work but may help give an idea of what I want. SELECT t1.checkid, t1.checkurl, t1.seconds, t2.checktime as checktime FROM Check_urls as t1 LEFT OUTER JOIN Check_log as t2 on t1.checkid = t2.checkid GROUP BY t1.checkid HAVING (checktime < (DYNAMICALLY INSERTED UNIX TIMESTAMP GETS INSERTED INTO HERE - t1.seconds) AND checktime = max(checktime)) OR checktime IS NULL ORDER BY checktime ASC Last edited by DigitalPimp; 01-20-2009 at 08:46 PM..