I was recently tasked with finding all the people in our CRM software that lack email addresses, and with around 3000 contacts, there was no way I was going to do it by hand. SQL to the rescue!
There was just one issue, email addresses are stored in a different table to allow each person to have multiple addresses on file. After a bit of digging around, I came up with this:
LEFT JOIN CRM.dbo.Emails
ON CRM.dbo.Emails.PersonId = CRM.dbo.People.PersonId
LEFT JOIN CRM.dbo.Companies
ON CRM.dbo.Companies.CompanyId = CRM.dbo.People.CompanyId
) AS T
WHERE T.EmailAddress IS NULL
Let’s go through this line-by-line so we can see exactly what it does.