Check for age of posts

zzz

I have a table (t1) that stores publications data and another table (t2) that stores articles data. The articles have datestamp 000-00-00. All article are associated with publications.

I need to run a script that will check and if there were no new articles posted under a particular publication for let say 30 days, change publication status from 'active' to 'inactive'.

So I guess I'll need to run a query something like

SELECT t1.publications
LEFT JOIN t2 ON (t1.publicationID = t2.publicationID)
WHERE t2.published_date > 30 days ..... ????

Need a mental nudge!

barand

SELECT t1.publications
LEFT JOIN t2 ON (t1.publicationID = t2.publicationID)
AND t2.published_date > CURDATE() - INTERVAL 30 DAY

zzz

Great. The only problem I see is if I have multiple articles and some are posted within the last 30 days and some prior, I'm afraid I'll get results showing. I need to make absolutely sure that there were not articles posted withing the last 30 days. Maybe I need to do GROUP by t1.publicationID?..

Also I tried changing 30 days to 10 and got less results, which I would expect the opposite...

barand

SELECT t1.publicationID, COUNT(t2.published_date) as total
LEFT JOIN t2 ON (t1.publicationID = t2.publicationID)
AND t2.published_date > CURDATE() - INTERVAL 30 DAY
GROUP BY t1.publicationID

zzz

I'm not sure what count is used for here?... Plus it causes some query error...

I guess what I need to do is to check what was the LATEST article in each publication posted and then check if it was posted 30 or more days ago. Sounds simple, but I'm not sure how to implement that.