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.
We’re looking to get data out of the SQL server, so select is the way to go.
This is the three fields of actual data we want back from the query.
this outer select will fetch data out of the LEFT JOIN that we’re about to construct.
These are the three fields that the left join will output. We need the email address here so that we can check if it is set to NULL in the outer select statement.
The select will start with the data from the People table
Then we fetch additional data from the Emails table
To make sure the rows are matched correctly, we compare the PersonID column of eachrow
Let’s get some more external data, from the Companies table this time
This time, we’re looking at the CompanyID, so that we can get the correct company name for the results
Save that sub-query as T
For each row in the sub-query, check if the EmailAddress is null
And that leaves us with this: