Re: Finding non-duplicate records?

From: Billz89 (Billz89_at_discussions.microsoft.com)
Date: 06/16/04

  • Next message: bob: "Web Deploy Error" 
    Date: Wed, 16 Jun 2004 09:28:30 -0700

    I modified your query to this...

    SELECT *
    FROM Contest
    WHERE (EMAIL NOT IN
                              (SELECT Email
                                FROM NewsLetter.dbo.pgd_Members))

    and received back 3,427 records from a 3,647 record database.

    To cover some areas I didn't in my original post:

    I'm running SQL 2000 on Win2k all the latest SPs

    We have two databases, each with a different table, Newsletter in the first database and Contest in the second database.

    Newsletter has the field Email and Contest has the field EMAIL.

    I want to:

    1. Remove duplicates from Contest.EMAIL (I think I can find something on that in this forum)

    2. Return the fields Contest.EMAIL, Contest.FName, Contest.LName where Contest.EMAIL does not exist in Newsletter.Email

    Example:

    Newsletter.Email(1) = John.Doe@foobar.com
    Newsletter.Fname(1) = John
    Newsletter.Lname(1) = Doe
    Newsletter.Email(2) = John.Deer@foobar.com
    Newsletter.Fname(2) = John
    Newsletter.Lname(2) = Deer

    Contest.EMAIL(1) = John.Doe@foobar.com
    Contest.Fname(1) = John
    Contest.Lname(1) = Doe
    Contest.EMAIL(2) = John.Deer@foobar.com
    Contest.Fname(2) = John
    Contest.Lname(2) = Deer
    Contest.EMAIL(3) = Barry.Doe@foobar.com
    Contest.Fname(3) = Barry
    Contest.Lname(3) = Doe

    I want to create a table with fields to accept Barry.Doe@foobar.com, Barry, Doe...

    Promotion.EMAIL(3) = Barry.Doe@foobar.com
    Promotion.Fname(3) = Barry
    Promotion.Lname(3) = Doe

    for those people who do not currently receive our newsletter.

    "Rohtash Kapoor" wrote:

    > 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
    >
    >
    >

  • Next message: bob: "Web Deploy Error"

    Relevant Pages