Non-matching records in T-SQL One-to-Many Relations

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:

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:

FirstName LastName CompanyName
System User NULL
Joe Bloggs Acme Ltd
John Smith HyperGlobalSoft
Jane Smith Consoto PLC

Leave a Reply

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