I need to extract a list of email addresses from a database. These email addresses are matched to systemIDs via a join, however an email address (in Table A) can correspond to 1+ usernames (in Table B) and each user name can correspond to either a NULL systemID or an existing systemID.

I am trying to extract ONLY the email addresses with NULL systemID and that are not matched to ANY systemID at all.

This is an example of data combinations:

**Email address**: aaa, bbb, ccc

**User names**: aaa1, aaa2, bbb1, bbb2, bbb3, ccc1

aaa1 -> linked to sysID; aaa2 -> NOT linked to sysID;

bbb1 -> linked to sysID; bbb2 -> linked to sysID; bbb3 -> NOT linked to sysID;

ccc1 -> NOT linked to sysID

**In this case I would only want to extract the email address "ccc"**

I have created two CTEs to split records with NULL and NOT NULL systemID, and now I would like to get all rows where the email in one CTE (with NULL systemID) is not contained in the other CTE (with NOT NULL systemID), but I can't find a way to do this. SSMS gives back the following error "The multi-part identified List\_NOTNULL\_systemid.email could not be bound". Please see the SQL statement in bold.

Here's my attempt:

```
WITH List_NOTNULL_systemid AS 
(
     SELECT 
         h.email as Email1, h.[Employee First Name], h.[Employee Last Name], 
         a.fullusername, r.SystemId, h.[Op Company Desc] 
     FROM 
         HR_CONTRACTORS_COMBINED AS h
     LEFT JOIN 
         AD_EMAIL_USERNAME_LINK AS a ON h.Email = a.mail
     LEFT JOIN 
         RPT_USER_INFO AS r ON a.fullusername = r.fullusername
     WHERE 
         h.email IS NOT NULL
         AND r.systemid IS NOT NULL
),
List_NULL_systemid AS 
(
    SELECT 
        h.email AS Email2, h.[Employee First Name], h.[Employee Last Name], 
        a.fullusername, r.SystemId, h.[Op Company Desc] 
    FROM 
        HR_CONTRACTORS_COMBINED AS h
    LEFT JOIN 
        AD_EMAIL_USERNAME_LINK AS a ON h.Email = a.mail
    LEFT JOIN 
        RPT_USER_INFO AS r ON a.fullusername = r.fullusername
    WHERE 
        h.email IS NOT NULL
        AND r.systemid IS NULL
)
SELECT * 
FROM List_NULL_systemid
WHERE Email2 NOT IN (List_NOTNULL_systemid.email1)

```

I also tried the following and it doesn't work:

```
SELECT * FROM List_NULL_systemid
WHERE Email2 NOT IN (email1)

```

Please help! Thank you!