Could you please review an SQL Server CTE not working?

Question

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!

Answer 1

here is one simple way :

SELECT
    h.email AS Email2
    , h.[Employee First Name]
    , h.[Employee Last Name]
    , a.fullusername
    , h.[Op Company Desc]
FROM
    HR_CONTRACTORS_COMBINED     AS h
    JOIN AD_EMAIL_USERNAME_LINK AS a
        ON h.Email = a.mail
WHERE
    NOT EXISTS
    (
        SELECT
            1
        FROM
            RPT_USER_INFO AS r
        WHERE
            a.fullusername = r.fullusername
            AND  r.SystemId  IS NOT NULL 
    );

Answer 2

Here is the mistake: WHERE Email2 NOT IN (List_NOTNULL_systemid.email1)

The fact that you use the alias List_NOTNULL_systemid that has been defined with CTE does not mean that you can refer to it directly. You still have to include it in the FROM clause:

With......
SELECT * 
FROM List_NULL_systemid
WHERE Email2 NOT IN (select email1 from List_NOTNULL_systemid)