query with "NOT IN" clause not returning results

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 with "NOT IN" clause not returning results

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-09-11 : 05:35:11

Hi,

I have this query, which is not returning the desired results. It's returning 0 results instead of 1000's.

I think by looking at this its pretty straightforward what I am trying to do. I have 3 tables of emails. Each table I am filtering against a do not email list, but when I do that nothing comes back.

Am I writin this incorrectly? Any help much appreciated!!

mike123

CREATE PROCEDURE dbo.select_mailingList

AS SET NOCOUNT ON

SELECT emailaddress FROM tblUserDetails WHERE mailinglist = 1 AND emailAddress NOT IN (SELECT emailAddress FROM tblEmail_Unsubscribe)
AND emailaddress NOT IN (SELECT bouncedEmailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1)

UNION

SELECT email FROM tblEmailList WHERE email NOT IN (SELECT emailAddress FROM tblEmail_Unsubscribe)
AND email NOT IN (SELECT bouncedEmailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1)

UNION

SELECT emailaddress FROM tbldeletedusers WHERE emailAddress NOT IN (SELECT emailAddress FROM tblEmail_Unsubscribe)
AND emailaddress NOT IN (SELECT bouncedEmailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1)

GO

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 05:44:02

[code]
CREATE PROCEDURE dbo.select_mailingList

AS SET NOCOUNT ON

SELECT emailaddress FROM tblUserDetails WHERE mailinglist = 1 AND
emailAddress NOT IN
(
SELECT emailAddress FROM tblEmail_Unsubscribe
Union
SELECT bouncedEmailAddress As emailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1
)

UNION

SELECT email FROM tblEmailList WHERE
emailAddress NOT IN
(
SELECT emailAddress FROM tblEmail_Unsubscribe
Union
SELECT bouncedEmailAddress As emailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1
)

UNION

SELECT emailaddress FROM tbldeletedusers WHERE
emailAddress NOT IN
(
SELECT emailAddress FROM tblEmail_Unsubscribe
Union
SELECT bouncedEmailAddress As emailAddress FROM NDR_EmailMessages WHERE NDR_TypeID = 1
)
[/code]

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 05:45:09

Try this

SELECT		a.eMailAddress
FROM		(
			SELECT	eMailAddress
			FROM	tblUserDetails
			WHERE	MailingList = 1
			UNION
			SELECT	eMail
			FROM	tblEmailList
			UNION
			SELECT	eMailAddress
			FROM	tblDeletedUsers
		) a
LEFT JOIN	(
			SELECT	eMailAddress
			FROM	tblEmail_Unsubscribe
			UNION
			SELECT	BouncedEmailAddress
			FROM	NDR_EmailMessages
			WHERE	NDR_TypeID = 1
		) i ON i.eMailAddress = a.eMailAdress
WHERE		i.eMailAddress IS NULL

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 05:45:44

Ouch me!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 05:55:05

If you want to see why the email addresses are not returning properly, use my code with alteration

SELECT		a.eMailAddress Source,
		i.eMailAddress Blocked
FROM		(
			SELECT	eMailAddress
			FROM	tblUserDetails
			WHERE	MailingList = 1
			UNION
			SELECT	eMail
			FROM	tblEmailList
			UNION
			SELECT	eMailAddress
			FROM	tblDeletedUsers
		) a
LEFT JOIN	(
			SELECT	eMailAddress
			FROM	tblEmail_Unsubscribe
			UNION
			SELECT	BouncedEmailAddress
			FROM	NDR_EmailMessages
			WHERE	NDR_TypeID = 1
		) i ON i.eMailAddress = a.eMailAdress

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-09-11 : 05:58:38

Hi guys,

Thanks alot for the quick help. Unfortunately I think there is a small error on both initial reponses. I get 0 results back on Chirag's response after changing one column from 'email' to 'emailaddress' after an error.

Peso I get this error when running yours. I'm sure its a very small error but I can't seem to figure it out =[ It doesn't seem to make much sense to me :S

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'eMailAdress'.

Thanks very much once again!! :D

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 06:11:06

I am just copying and pasting from your initial code

SELECT		a.eMailAddress
FROM		(
			SELECT	eMailAddress
			FROM	tblUserDetails
			WHERE	MailingList = 1
			UNION
			SELECT	eMail
			FROM	tblEmailList
			UNION
			SELECT	eMailAddress
			FROM	tblDeletedUsers
		) a
LEFT JOIN	(
			SELECT	eMailAddress
			FROM	tblEmail_Unsubscribe
			UNION
			SELECT	BouncedEmailAddress
			FROM	NDR_EmailMessages
			WHERE	NDR_TypeID = 1
		) i ON i.eMailAddress = a.eMailAddress
WHERE		i.eMailAddress IS NULL

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 06:17:45

What you need to do, is to check if the column names are correctly spelled for each SELECT.

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-09-11 : 06:18:13

that fixed it, thanks !

(still can't figure it out but hey its 3am and been a long day!)

have a great day peter,

Much appreciated

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 06:19:11

Did the query now return the emailaddresses you want? Some 1000+ ?

I would also like to know if there were any speed improvment and/or any change in the execution plan between this approach and my suggestion.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 06:28:28

There were a discussion here for a while ago, that there might be a bug using IN, and misspelling the column names in the IN clause, and SQL Server used the misspelled column name from the table oustide the IN. I can't remember the topic but if you are interested, you can search SQLTeam.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources