[thelist] MySQL - selecting records that are NOT in a date range...

r937 rudy at r937.com
Tue Nov 20 05:02:27 CST 2007

> But how can I say to MySQL: get me all users who DON'T have entries in
> this range...

the other suggestions have all been to find users which have activities 
outside the given range, which is ~not~ the same thing as all users with no 
activities in the given range

for this problem, you cannot just query the activities table for something 
that isn't there

you'll need to find the userids from another source, and the only thing 
which will work here is the users table

match the users table to the given range of activities, and keep the userids 
which don't have a match

there are two common ways to do it -- a NOT EXISTS subquery, or a LEFT OUTER 
JOIN with an IS NULL test on the join column

   SELECT Users.UserId
     FROM Users
     JOIN activities
       ON activities.UserId = Users.UserId
      AND activities.`Timestamp` > '1188604800'
      AND activities.`Timestamp` < '1191196799'
    WHERE activities.UserId IS NULL


More information about the thelist mailing list