[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
LEFT OUTER
JOIN activities
ON activities.UserId = Users.UserId
AND activities.`Timestamp` > '1188604800'
AND activities.`Timestamp` < '1191196799'
WHERE activities.UserId IS NULL
rudy
http://r937.com/
More information about the thelist
mailing list