Post reply

Trouble with DISTINCT

  • November 5, 2009 at 4:34 am

    #206300

    I'm trying to return data for a query that requires all data to be returned, however, the data must be filtered on a single column to filter out duplicates. The query I have so far is:

    The subquery works, however, I'm struggling on how to code the outside query without creating DISTINCT on all columns? I want to filter out duplicates in the tblTemporaryUsers.Email column only, no other duplicates at this stage matter.

    SELECT tblTemporaryUsers.FirstName, tblTemporaryUsers.Surname, tblTemporaryUsers.Email, tblTemporaryUsers.Password, tblTemporaryUsers.Password AS Pass, tblTemporaryUsers.CompanyID

    FROM tblTemporaryUsers

    WHERE Email IN (SELECT DISTINCT tblTemporaryUsers.Email FROM tblTemporaryUsers, tblUsers WHERE tblTemporaryUsers.Email<>tblUsers.Email)

    What I'm trying to achieve - basically, all new users (identified by email address) will be copied into the tblUsers table, and duplicates should be discarded.

    Any help would be great!

  • November 5, 2009 at 4:38 am

    #1075245

    You've posted in a 7/2000 forum , is that correct ?


    Clear Sky SQL
    My Blog[/url]

  • November 5, 2009 at 4:40 am

    #1075246

    Apologies, no it's not. I meant to post in SQL 2005. Can a mod move the thread please? Thanks

  • November 5, 2009 at 5:00 am

    #1075249

    Try this...

    Should be ok.

    with cteNewUsers

    as

    (

    SELECT tblTemporaryUsers.FirstName, tblTemporaryUsers.Surname,

    tblTemporaryUsers.Email, tblTemporaryUsers.Password, tblTemporaryUsers.Password AS Pass,

    tblTemporaryUsers.CompanyID ,

    row_number() over (partition by tblTemporaryUsers.Email order by select(1)) as rown

    FROM tblTemporaryUsers

    WHERE not exists (SELECT tblUsers.Email FROM tblUsers WHERE tblTemporaryUsers.Email = tblUsers.Email)

    )

    insert into tblUsers

    select * from cteNewUser where rown = 1


    Clear Sky SQL
    My Blog[/url]

  • November 5, 2009 at 5:15 am

    #1075255

    Thanks Dave, I'll have to tweak it a little - I am retrieving the results and putting it into a DataTable to add some columns, etc, before then inserting into the database using BulkCopy and ColumnMappings.

    Would I just need to omit the final "insert into ...." statement?

  • November 5, 2009 at 5:28 am

    #1075265

    Yes you will , sorry i just assumed you needed them pushed into the user table


    Clear Sky SQL
    My Blog[/url]

  • November 5, 2009 at 5:31 am

    #1075268

    Great, thanks Dave 🙂 Hopefully it all works....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply