Can anyone help me with an SQL query? - GoFuckYourself.com

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

psili · 01-20-2009, 08:54 PM

I can't write a query to save my life or have data to test against. So, if you run the following, I hope you have a backup or expect errors... otherwise, here's a bump for you.

Code:

SELECT 
 t1.checkid, 
 t1.checkurl, 
 t1.seconds, 
   t2.checktime as checktime 
FROM 
 Check_urls as t1 
   LEFT JOIN Check_log as t2 on t1.checkid = t2.checkid 
 WHERE (t2.checkid is null OR (t1.checktime < ( {dynamic_timestamp}-t1.seconds ) ) )

DigitalPimp · 01-20-2009, 09:03 PM

thanks for the bump and effort. The log table contains multiple entries for each URL and I want the query to return only those records with the most recent timestamps for each URL and ignore the rest of the older ones thus the need for something like max(checktime).

01-20-2009, 08:44 PM	#1
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..

01-20-2009, 08:54 PM	#2
psili Confirmed User Join Date: Apr 2003 Location: Loveland, CO Posts: 5,526	I can't write a query to save my life or have data to test against. So, if you run the following, I hope you have a backup or expect errors... otherwise, here's a bump for you. Code: SELECT t1.checkid, t1.checkurl, t1.seconds, t2.checktime as checktime FROM Check_urls as t1 LEFT JOIN Check_log as t2 on t1.checkid = t2.checkid WHERE (t2.checkid is null OR (t1.checktime < ( {dynamic_timestamp}-t1.seconds ) ) ) __________________ Your post count means nothing.

01-20-2009, 09:03 PM	#3
DigitalPimp Confirmed User Industry Role: Join Date: Jun 2003 Location: Los Angeles, CA Posts: 512	thanks for the bump and effort. The log table contains multiple entries for each URL and I want the query to return only those records with the most recent timestamps for each URL and ignore the rest of the older ones thus the need for something like max(checktime).