This blog post is a continuation from the previous blog post called SQL Finding Duplicates across Two Tables One Column

Let’s change the conditions. Suppose no one person can use the same UniqueNumber more than once, and further suppose that it is fine if more than one person has used the same UniquenNumber. So, it’s okay if both Polly and Jill have used the same UniqueNumber 23418 at some point. However, a person cannot use the same UniqueNumber more that one time, ever. A duplicate is now defined as a combination of Name and UniqueNumber. I like to think about it this way: If you concatenate the Name with UniqueNumber you get something like John12987 or Frank23456. Now you are comparing two tables with the concatenated string and a duplicate is found when the concatenated strings are equivalent.

Here is the query in Access

Here is the SQL code for the above query.

SELECT New.FirstName, New.Amount, New.UniqueNumber, History.FirstName, History.Amount, History.UniqueNumber
FROM New LEFT JOIN History ON (New.FirstName = History.FirstName) AND (New.[UniqueNumber] = History.[UniqueNumber])
WHERE (((History.FirstName) Is Not Null) AND ((History.UniqueNumber) Is Not Null));

Here is the results of our query on the same data as the previous post.

Below is the data in the two tables.

John      12        12987     
Frank     121       23456     
Sally     647       11211     
Bob       24        98700     
Sally     7876      22222     

This table is the New table.

Sally     345       12555     
Frank     232       23456