SELECT a.Contact_Email
FROM (
        SELECT b.Contact_Email
        FROM [OAG_Profile_Expansion_Master] b
        WHERE 1 = 1
        AND LOWER(
            RIGHT (
                b.Contact_Email,
                LEN(b.Contact_Email) - CHARINDEX('@', b.Contact_Email)
            )
        ) IN (
            SELECT LOWER(x.Domain)
            FROM ent.[Dealer Domains] x
    )
) a
 
UNION ALL
SELECT a.Contact_Email
FROM (
    SELECT b.Contact_Email
    FROM [OAG_Profile_Expansion_Master] b
    WHERE 1 = 1
    AND LOWER(
        RIGHT (
            b.Contact_Email,
            LEN(b.Contact_Email) - CHARINDEX('@', b.Contact_Email)
            )
        ) IN (
            SELECT LOWER(x.Domain)
            FROM ent.[Cat_Agency_Domains] x
    )
) a
 
UNION ALL
SELECT a.Contact_Email
FROM (
    SELECT b.Contact_Email
    FROM [OAG_Profile_Expansion_Master] b
    WHERE 1 = 1
    AND LOWER(
        RIGHT (
            b.Contact_Email,
            LEN(b.Contact_Email) - CHARINDEX('@', b.Contact_Email)
            )
        ) IN (
            SELECT LOWER(x.Domain)
            FROM ent.[Competitor Domains] x
    )
) a