Compare two MS SQL Tables for matches

I need to compare two MS SQL 2005 tables to find records in table 1 that exist in table 2.

Table 1 was the original table in the original database.
I backed up Table 1, deleted all the records then started adding new records with no change to the structure.

Table 2 is in a new database I just created and restored the backup from the original database.

The two tables I want to compare are identical in structure.  Table 2 has three years worth of old personnel records with key id numbers that begin with the year as 200800001 or 200900001.  Table 1 has only this years records and key ids start with the year as 201000001.  That's not really important though except that "John Smith" with Employee Number "12345" may be in the old database more than once if he was here for more than one year, e.g. 200800056-John Smith-12345 and 200900081-John Smith-12345 are the same person with two records in the old database.

I need to compare Table 1 (the new one) with Table 2 and list all the people who are in Table 1 who are also in Table 2.  I only need to compare FirstName, LastName and SSN although just SSN may suffice.  

I don't know if this could be done at the same time or not, but I need to know if "John Smith" from Table 1 is not only in Table 2, but how many times (just looking at name and SSN fields or just SSN).

SYEP.dbo.Applicants is Table 1
SYEP_BAK_09.dbo.Applicants is Table 2

LastName, FirstName and SSN are the relevant fields

keyApplicantID is the primary key field (200800001..., 200900001... in Table 2 and 201000001... in Table1) perhaps not relevant.

The purpose of the query is so that I can list the people in this year's program (Table 1 - 201000000s) who participated in at least two previous years (Table 2 - 200800000s and 200900000s)

Thank you very much in advance for your assistance.
Who is Participating?
Kelvin McDanielConnect With a Mentor Sr. Developer/ConsultantCommented:
I believe the following is what you need:

    A1.keyApplicantID, A1.LastName, A1.FirstName, A1.SSN
    , (
        SELECT COUNT(*)
        FROM SYEP_BAK_09.dbo.Applicants AS A3
        WHERE A3.LastName = A1.LastName AND A3.FirstName = A1.FirstName AND A3.SSN = A1.SSN
      ) AS TotalCount
    SYEP.dbo.Applicants AS A1 INNER JOIN SYEP_BAK_09.dbo.Applicants AS A2
        ON A1.LastName = A2.LastName AND A1.FirstName = A2.FirstName AND A1.SSN = A2.SSN
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
You can use a WHERE EXISTS if looking at existence by a primary key OR for the distinct / uniqueness of entire rows you can use INTERSECT.

Syntax would be :

select {column list} from tablea
select {column list}  from tableb

megninAuthor Commented:
That's a beautiful thing!  Exactly what I need.
Something I noticed when I was experimenting earlier was that in Microsoft SQL Server Management Studio if I have the database (SYEP2007) selected as the default and I reference a table the same database by the full SYEP2007.dbo.Applicants "path" it says that it's an invalid table name.  I just call it "Applicants" and it works just fine though.
Thank you so much that was perfect!
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

megninAuthor Commented:
mwvisa1, azarc3 beat you to the punch.  ;-)
Kevin CrossChief Technology OfficerCommented:
Sorry, I just saw the "how many times" piece which makes the first posted suggestion make more sense to me.  That subquery should be fine, but since you have to do a JOIN anyway, I would also consider like this:

SELECT a.keyApplicantID, a.LastName, a.FirstName, a.SSN
    , b.TotalCount
FROM SYEP.dbo.Applicants a
   SELECT SSN, COUNT(*) AS TotalCount
   FROM SYEP_BAK_09.dbo.Applicants
) b ON a.SSN = b.SSN

Hope that helps!
megninAuthor Commented:
azarc3, your solution was just what I needed.

mwvisa1, thank you for your suggestion.  I will likely make use of that technique somewhere.
megninAuthor Commented:
Yes, that makes sense as well.  Thank you again.
Kevin CrossChief Technology OfficerCommented:
You are most welcome!
@azarc3, good work...

Best regards and happy coding,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.