MYSQL - Selecting existing users who have not made an entry in the last 3 months, but have made an entry within the last 6 months

After several hours of searching I'm finally giving in and asking for help. I've found something that gets me close, but doesn't seem to be working properly.

Basically looking for users who uses us between 3-6 months ago, but haven't in the last 3 months.

For the sake of the example lets say I have two tables named event(id, clientFK, insertion_date, officeFK) and client(id, name):

SELECT DISTINCT e.id, e.insertion_date, c.name
FROM event e
LEFT JOIN client c ON c.id = e.clientFK
WHERE e.id NOT IN (   
SELECT e.id
FROM event e
WHERE e.insertion_date
BETWEEN (
NOW( ) - INTERVAL 3
MONTH
)
AND NOW( )
)
AND e.insertion_date > ( NOW( ) - IN开发者_如何转开发TERVAL 6
MONTH )
AND officeFK =1
ORDER BY e.insertion_date DESC

**UPDATE*** id is just an auto incrementing column so obviously it would never show up using the logic above. I meant to search for the clientFK in the event table. Based off the first response below I came up with this:

SELECT DISTINCT e.clientFK, e.insertion_date, c.name
FROM event e
LEFT JOIN client c ON c.id = e.clientFK
WHERE e.clientFK NOT IN (
  SELECT e.clientFK FROM event e
  WHERE e.insertion_date > (NOW() - INTERVAL 3 DAY)
)
AND e.insertion_date BETWEEN (NOW() - INTERVAL 3 DAY) AND (NOW() - INTERVAL 6 MONTH)
AND officeFK =1
ORDER BY e.insertion_date DESC

However, even when narrowing the NOT IN subquery to just 3 days I'm still returning 0 rows. Changing NOT IN to IN also results in 0 rows returned. There are thousands of rows in my events table where the clientFK is "NOT IN" the last 3 days. I've got to be doing something wrong somewhere.

CORRECT CODE BELOW:

SELECT DISTINCT e.id, e.insertion_date, c.name
FROM event e
JOIN client c ON c.id = e.clientFK
WHERE e.clientFK NOT IN (
  SELECT e.clientFK FROM event e
  WHERE e.insertion_date > (NOW() - INTERVAL 3 MONTH)
)
AND e.insertion_date < (NOW() - INTERVAL 3 MONTH)
AND insertion_date > (NOW() - INTERVAL 6 MONTH)
AND e.officeFK = 1
ORDER BY e.insertion_date DESC

There are several ways to do this. But I think you're close. You don't seem to have the date range correct for 3-6 months in your primary query. You were checking for anything from 6 months until now. That doesn't match the spec of the OP - users of an event 3-6 months ago, but not within the last 3 months. Logically it seems the same, but try this.

SELECT DISTINCT e.id, e.insertion_date, c.name
FROM event e
JOIN client c ON c.id = e.clientFK
WHERE e.clientFK NOT IN (
  SELECT e.clientFK FROM event e
  WHERE e.insertion_date > (NOW() - INTERVAL 3 MONTH)
)
AND e.insertion_date BETWEEN (NOW() - INTERVAL 3 MONTH) AND (NOW() - INTERVAL 6 MONTH)
AND e.officeFK = 1
ORDER BY e.insertion_date DESC