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