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:
SELECT T.FirstName, T.LastName, T.CompanyName FROM ( SELECT CRM.dbo.Companies.CompanyName, CRM.dbo.People.FirstName, CRM.dbo.People.LastName, CRM.dbo.Emails.EmailAddress FROM CRM.dbo.People 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.
T.FirstName, T.LastName, T.CompanyName
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.
SELECT CRM.dbo.Companies.CompanyName, CRM.dbo.People.FirstName, CRM.dbo.People.LastName, CRM.dbo.Emails.EmailAddress
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
LEFT JOIN CRM.dbo.Emails
Then we fetch additional data from the Emails table
ON CRM.dbo.Emails.PersonId = CRM.dbo.People.PersonId
To make sure the rows are matched correctly, we compare the PersonID column of eachrow
LEFT JOIN CRM.dbo.Companies
Let’s get some more external data, from the Companies table this time
ON CRM.dbo.Companies.CompanyId = CRM.dbo.People.CompanyId
This time, we’re looking at the CompanyID, so that we can get the correct company name for the results
) AS T
Save that sub-query as T
WHERE T.EmailAddress IS NULL
For each row in the sub-query, check if the EmailAddress is null
And that leaves us with this: