query not returning desired results -NOT IN clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

query not returning desired results -NOT IN clause

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-10 : 03:55:00

Hi,

I have a query that was returning the proper results, however since I have extended the WHERE clause it's returning 0 rows. I guess I am not forming the query properly, but I'm unsure of how to fix it.

This returns the proper results

SELECT userID, nameOnline, emailaddress, active, date, guid FROM tblUserDetails WHERE active = 8

This below returns 0 results. What I am attempting to do is make sure no results are brought back that are found in the EmailMessages table.

SELECT userID, nameOnline, emailaddress, active, date, guid FROM tblUserDetails WHERE active = 8 AND emailaddress NOT IN (SELECT bouncedEmailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1) UNION

Can anyone guide me on this one? Thanks very much once again :)

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 03:59:50

What is the last UNION doing there?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 04:01:44

Is there a possibility somewhere that emailaddress is NULL?
If so, try to add AND bouncedEmailAddress IS NOT NULL in the IN clause.

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-11-10 : 04:11:39

Hi Peso,

Sorry the UNION was left there by accident. I checked the tables and I do have some NULL values in NDR_EmailMessages.

Adding your recommnedation fixed the problem, as seen below.
Altho I don't completely understand how having a NULL value in this table filters out non NULL values in the "tblUserDetails" table, the correct results do seem to be returned.

Thanks very much! [ once again! ;) ]

mike123

SELECT userID, nameOnline, emailaddress, active, date, guid FROM tblUserDetails WHERE active = 8 AND emailaddress NOT IN (SELECT bouncedEmailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1 AND bouncedEmailAddress IS NOT NULL)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 04:21:14

Well, NULL is a special state of mind.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources