problem with not exists/ not in
Posted on 2008-09-30
I have posted this question in both the mysql forum and the mssql forum since this is a basic SQL question.
I have two tables: userinfo and moneytrans.
The userinfo contains (amongst others) a userID, a lastvisit date and a current balance.
The moneytrans contains information about moneytransactions - that is (amongst others) a transactiontype (type) and a transactiondate (logdate)
(Yes, i know the database has an unfortunate design, but it's hard to change that right now...)
I want all the users that have not been active for 3 months (userinfo.lastvisit is more than 3 months ago) but has a balance > 0 and has not had a transaction of transactiontype 149 for the past 30 days.
the simplest way for me to write that would be:
SELECT distinct u.ID, u.money_balance
FROM trendsalesdk.userinfo u
WHERE u.LastVisit < DATE_SUB(CURDATE(), INTERVAL 3 Month)
AND u.Money_Balance > 0.00
AND (u.Active > 0 OR u.active=-2)
u.id not in (
select userid from moneytrans m2
WHERE m2.LogDate <= DATE_SUB(CURDATE(), INTERVAL 30 Day)
AND m2.Type = 149
AND m2.Accept = 1
AND m2.Done = 1
yes, i know I should rewrite that to a left join, but i have simplified my query to this . I think it should work but it doesn't.
when I execute this statement in phpmyadmin, I get almost all users (almost 400.000 users) - even though the first select statement alone returns a considerably smaller amount of users. That makes no sense to me.
1) Why does my query fail?
2) Can someone help me write a query that actually produces the job effectively? I'm getting slightly desperate...