SQL: Find entries that don't exist

Question

I know this sounds backwards but I am looking for a way to search groups of records where a group does not contain a particular field.

We receive email data from multiple companies, parse the data into our database and then retrieve the data for our use. For example, an email might come through with Name, Address, City, State, Zip and Phone. Each of these properties are stored in a different row with the rows being associated by a common email_id (I know how inefficient this is, I inherited this and am changing it in the near future).

Here is an example of some rows:

ID         EmailID          Field_Name             Field_Value
1234       4556             Name                   Joe Bob
1235       4556             Address                123 Main St.
1236       4556             City                   New York
1237       4556             State                  New York
1238       4556             Zip                    01234
1239       4556             Phone                  1112223333
1240       4557             Name                   Joe Bob
1241       4557             Address                123 Main St.
1242       4557             City                   New York
1243       4557             State                  New York
1244       4557             Zip                    01234
(no phone field for email_id 4557 on purpose)

When I retrieve this data I specify the fields in the SELECT statement (SELECT Name, Address, ...) and have recently started to get an error of "Phone field not found" which I believe means that one of these groups of emails did not have a 'Phone' field entered.

My dilemma is how to search for something that is missing. I would like to be able to run a query that would return the emailID of any email that does not have an associated 'Phone' field. Using the data from above the query would return emailID 4557 since it has no Phone field.

I am on MSSQL Server 8 if that matters.

TIA, Brian

Answer 1

You could use either a LEFT OUTER JOIN or a NOT EXISTS to get the offending records.

LEFT JOIN

SELECT  *
FROM    YourTable yt
        LEFT OUTER JOIN (
          SELECT  EmailID     
          FROM    YourTable
          WHERE   Field_Name = 'Phone'
        ) yte ON yte.EmailID = yt.EmailID
WHERE   yte.EmailID IS NULL

NOT EXISTS

SELECT  *
FROM    YourTable yt
WHERE   NOT EXISTS (
          SELECT  EmailID     
          FROM    YourTable
          WHERE   Field_Name = 'Phone'
                  AND yt.EmailID = EmailID
        ) 

A good read about the difference (similarity) about both methods can be found here