Post reply

finding distinct data between two tables

  • March 20, 2007 at 2:08 pm

    #176530

    I need to compare some data between two tables and find the data in one table that is not in the other.

    Here's the scenario:

    Table1 has 1500 records.

    Table2 has 1000 records.

    I need to find which records from Table2 are in Table1, but the data I want returned is which records are in Table1 but not in Table2.

    I have no idea how to write a comparison between the two and spit out the difference.

    Thanks for your help,

    David

  • March 20, 2007 at 2:53 pm

    #696049

    select table1.* from table1 left join table2 on table1.pk = table2.pk where table2.pk is null

    pk is whatever field is common between the 2 tables

  • March 20, 2007 at 2:55 pm

    #696050

    Hi David,

    One way to do this is to do a left join and then check for null on the right handle columns, for example

    SELECT Table1.* FROM

    Table1

    LEFT JOIN

    Table2

    ON Table1.ID = Table2.ID

    WHERE Table2.ID IS NULL

    This query will return all of the records in Table1 which are not in table2. You can reverse it (or do a full join) to get records which exists in either Table1 or Table2 but not both.

    Red Gate also do a tool called SQL Data Compare which will compare data in two tables tell you which records differ and generate a synchronisation script automatically for you.

    Hope this helps,

    - James

    --
    James Moore
    Red Gate Software Ltd

  • March 21, 2007 at 7:34 am

    #696141

    PERFECT guys!

    Thanks so much.

Viewing 4 posts - 1 through 3 (of 3 total)

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