"Not In" SELECT query not working 1

[DoubleWeb]

Hello all,
I had this query working for 2 years with no problem and now, all of a sudden it can't find the records anylonger.

The Table "WWTester" has 17 records. I have a query that reads:

SELECT WWTESTER.homephone, WWTESTER.firstname, WWTESTER.lastname
FROM WWTESTER
WHERE (((WWTESTER.homephone) In (SELECT Homephone FROM guesttrack_clean)));

The Table "guesttrack_clean" is another table used just to compare data. This query above retrieves me 5 records and the records are perfectly fine.

Now, I have another query that does the opposite, like that:

SELECT WWTESTER.homephone, WWTESTER.firstname, WWTESTER.lastname
FROM WWTESTER
WHERE (((WWTESTER.homephone) Not In (SELECT Homephone FROM guesttrack_clean)));

And thats where the problem is.. Its not giving me any records at all. Should give me 12 records Correct? Exactly the records not in the first query I listed above.

Any clues...
Thanks a lot!
DBWEB

 

[traingamer]

Are the WWTESTER.homephone that are not showing up null by any chance?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[DoubleWeb]

No Greg.. The homephone field can't be null on that table and I've already "manually" tested and found the records that are NOT in the guesttrack_clean table. I searched for homephone numbers that ARE in the WWTester table and NOT in the guesttrack_clean table..

Its weird that the IN (SELECT... query works and the NOT IN (SELECT... doesn't.

 

[PHV]

You may try either this:
SELECT WWTESTER.homephone, WWTESTER.firstname, WWTESTER.lastname
FROM WWTESTER
WHERE Not (WWTESTER.homephone In (SELECT Homephone FROM guesttrack_clean))
Or this:
SELECT WWTESTER.homephone, WWTESTER.firstname, WWTESTER.lastname
FROM WWTESTER LEFT JOIN guesttrack_clean ON WWTESTER.homephone = guesttrack_clean.Homephone
WHERE guesttrack_clean.Homephone Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DoubleWeb]

PHV,
Thanks Once again! The second option you gave me worked. BUt the first one did not.
Could you tell me why or what is wrong with my query that does not work?

I Appreciate it!