Comparing email records between two tables

I am trying to compare a table of users from one DB
and check whether that Email Address exists in our Dynamics CRM user base

I have the table User_V from that other interface,
and I created the following query, but it only gives me the results which are useremail = NULL
and I am only trying to find those who exist in user_v and not in [SystemUserBase]

 select *
       from [HSchool].[dbo].[user_v] as u
       where   not exists (select InternalEMailAddress
       from [HrProd_MSCRM].[dbo].[SystemUserBase] as inn 
where ltrim (rtrim (LOWER (u.useremail))) collate database_default <> ltrim (rtrim (LOWER(inn.InternalEMailAddress))))

I hope I am as clear as possible,
thank you in advance!

1 Answer

You are doing a double negative.

select *
from [HSchool].[dbo].[user_v] as u
where not exists (select InternalEMailAddress
from [HrProd_MSCRM].[dbo].[SystemUserBase] as inn
where ltrim (rtrim (LOWER (u.useremail))) collate database_default <> ltrim (rtrim (LOWER(inn.InternalEMailAddress)))

This query finds records that does not match.

Then there is a “Not Exist” above that.
Amending the “Where” clause in the sub-query to an equate should work

Archive from: https://stackoverflow.com/questions/59018118/comparing-email-records-between-two-tables

Leave a Reply

Your email address will not be published. Required fields are marked *