Re: Finding non-duplicate records?

From: Rohtash Kapoor (rohtash_nospam_at_sqlmantra.com)
Date: 06/16/04 

Date: Tue, 15 Jun 2004 23:10:04 -0700

CREATE TABLE Newsletter
(
      Email VARCHAR(25)
)

INSERT INTO NewsLetter VALUES ('one@domain.com')
INSERT INTO NewsLetter VALUES ('two@domain.com')
INSERT INTO NewsLetter VALUES ('three@domain.com')
INSERT INTO NewsLetter VALUES ('four@domain.com')
INSERT INTO NewsLetter VALUES ('five@domain.com')
INSERT INTO NewsLetter VALUES ('six@domain.com')
INSERT INTO NewsLetter VALUES ('seven@domain.com')
INSERT INTO NewsLetter VALUES ('eight@domain.com')
INSERT INTO NewsLetter VALUES ('nine@domain.com')
INSERT INTO NewsLetter VALUES ('ten@domain.com')

CREATE TABLE Contest
(
    Email VARCHAR(25)
)

INSERT INTO Contest VALUES ('one@domain.com')
INSERT INTO Contest VALUES ('three@domain.com')
INSERT INTO Contest VALUES ('five@domain.com')
INSERT INTO Contest VALUES ('seven@domain.com')
INSERT INTO Contest VALUES ('eight@domain.com')

INSERT INTO Contest VALUES ('one@sqlmantra.com')
INSERT INTO Contest VALUES ('three@sqlmantra.com')
INSERT INTO Contest VALUES ('five@sqlmantra.com')
INSERT INTO Contest VALUES ('seven@microsoft.com')
INSERT INTO Contest VALUES ('eight@microsoft.com')

SELECT * FROM Newsletter
SELECT * FROM Contest

SELECT * FROM Contest
WHERE Email NOT IN (SELECT Email FROM Newsletter) 

---
Rohtash Kapoor
http://www.sqlmantra.com
"Billz89" <Billz89@discussions.microsoft.com> wrote in message
news:07E68452-E7FD-4B87-9786-8F8B238B6A71@microsoft.com...
> Hello.
>
> I have two tables and in each e-mail addresses; one for newsletter
subscribers, the other for contest entrants.  I want to send a special
one-time-only newsletter to the contest entrants who are not subscribers of
our newsletter.  I have been able to pull the duplicate records for the
tables, now I just need to pull the non-duplicate e-mails from the contest
table.  I tried this:
>
> SELECT     Contest.EMAIL AS Expr1
> FROM         Contest CROSS JOIN
>                       NewsLetter.dbo.pgd_Members
> WHERE     (NOT EXISTS
>                           (SELECT     EMAIL
>                             FROM          Contest
>                             WHERE      NewsLetter.dbo.pgd_Members.Email =
Contest.EMAIL))
>
> I created the above based on research I did in this forum, but I did
something wrong as the query is stuck in a loop.  I stopped at over 1
million rows.  The Contest table has 4,000 records.
>
> Thanks for the help.
>
> Bill

Relevant Pages