Compare Two SQL Tables and Find Missing Data

I have two tables Users and tmp_jasUsers. I need to know which of tmp_JasUsers are NOT in the Users table. The common field is email (email in users and emailAddr in tmp_jasUsers). This one has me stumped! Thanks in advance.
tryportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I need to know which of tmp_JasUsers are NOT in the Users table. The common field is email
Lots of different ways to pull this off, here's a couple

SELECT tju.*
FROM tmp_jasUsers tju
   -- LEFT means select all rows from tju ...
   LEFT JOIN Users u ON u.Email = tju.emailAddr
-- That don't have a matching u 
WHERE u.Email IS NULL

-- or the IN approach

SELECT * FROM tmp_jasUsers WHERE emailAddr NOT IN (SELECT Email FROM Users)

Open in new window

0
tryportAuthor Commented:
Thank you. I left out a part of my question - or in fairness to you I could ask it separately - how can I also know if there are duplicates (and which ones are duplicates) from the tmp_jasUsers in the user table?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'duplicates'.  
   In tmp_jasUsers table more than once?
   In tmp_jasUsers table, and also in Users table once?
   In tmp_jasUsers table, and also in Users table more than once?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tryportAuthor Commented:
In the tmp_jasUserTable once and in the User table more than once
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ok.  Give this a whirl...

SELECT * 
FROM tmp_jasUsers 
WHERE emailAddr IN (
   SELECT Email
   FROM Users
   GROUP BY Email
   HAVING COUNT(Email) > 1)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tryportAuthor Commented:
Thanks so much for sticking with me and for all of the help!
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw, couple of articles that may interest you, hit the 'Was this article helpful' button if you liked them.
SQL Server Delete Duplicate Rows Solutions, explains how to select/soft delete/hard delete duplicate rows.
SQL Server GROUP BY Solutions, explains the HAVING clause

Thanks for the grade, good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.