problem with not exists/ not in

Posted on 2008-09-30
Last Modified: 2012-05-05
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
    and ( 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...
Question by:RunePerstrup
LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 22605187
what about this:
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
    and ( not in (
        select userid from moneytrans m2
          AND m2.Type = 149
          AND m2.Accept = 1
          AND m2.Done = 1

Open in new window


Author Comment

ID: 22605553
I seem to have used a "lesser than" instead of a "greater than". Silly me

As for PHPMyadmin, it still gives the wrong result, but in a command prompt i works fine. Yet another reason Never to use phpmyadmin.

Thanks :-)

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now