mysql query to retrieve rows based on different keys and timestamps

we have a "transactions" table that开发者_StackOverflow's recording a variety of events happening on our website. One in particular needs a nightly "cleanup" process run on it. Here's my columns (simplified):

id, txName, brandName, visitorID, txDate

Here's what I want to retrieve (in prose):

"READ" txName's from vistiorID's that haven't recorded a "READ" txName in the last 6 weeks (based on txDate) for a particular brandName. It's entirely possible (and likely) that visitorID will have "READ" txName's for more than one brandName during the 6 week window. We only have 5 brandName's so I'm totally content building a process for a single brandName and repeating it 5 times.

My brain can only stretch as far as doing 2 MySQL SELECT queries (records prior to 6 weeks, records within 6 weeks), then looping through the two sets a few times to get an array of final records that have to be processed. But I'm sure there's a WAAAAY more efficient way that I just don't understand.

I hope this makes sense...thank you anybody that can help. I think the part that's really messing me up is trying to select people who haven't logged a transaction, versus those who have

You could try something like:

  SELECT DISTINCT(visitorID) 
    FROM transactions 
   WHERE txName = "READ" 
     AND txDate <= '$6weeksago'   
GROUP BY brandName

Where $6weeksago is a variable created in PHP to represent the time of the 6 week cut off in the required format (not sure if it was a date or Unix timestamp).

I haven't tested this as I don't have the table, but I think it is what you are after!