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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

777 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