STUCK -- query conditions

zzz

I am still trying to figure out a way to solve this.

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.

t1
----------------
t1.publicationID
t1.publicationName
t1.status

t2
----------------
t2.articleID
t2.publicationID
t2.articleName
t2.published_date

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'.

I was suggested to do something like this:

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

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. It appears i need some sort of GROUP BY condition.

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. Sort of stuck.

laserlight

Perhaps something like this would work:

UPDATE t1 SET status='inactive' WHERE publicationID NOT IN
(SELECT t1.publicationID FROM t1
LEFT JOIN t2 ON (t1.publicationID = t2.publicationID)
WHERE t2.published_date > CURDATE() - INTERVAL 30 DAY)

zzz

hah... I've never thought this kind of syntax was possible... looks very interesting and i'm going to try to give it a whirl.

However, I do need to make a select query, because I need to gether some basic info and email it before I change the status.