SELECT e.uniqueid ,
COALESCE(e.email1, e.email2, e.email3, '') AS email
FROM dbo.email e
WHERE NOT EXISTS
(
SELECT * FROM dbo.unsubscribe u
WHERE u.email = e.email
AND e.email_unsubscribe = 'Y'
);
How do I get the records with no email address to not show up? Otherwise it is working
select e.SOCIALSEC as SSN, COALESCE(ltrim(rtrim(e.EMAIL1)), ltrim(rtrim(e.EMAIL2)), ltrim(rtrim(e.EMAIL3)),'') as EMAIL_ADDRESS
from dbo.ACCOUNT e
WHERE NOT EXISTS
(
SELECT *
FROM dbo.EMAIL_UNSUBSCRIBE_LIST u
WHERE COALESCE(ltrim(rtrim(e.EMAIL1)), ltrim(rtrim(e.EMAIL2)), ltrim(rtrim(e.EMAIL3)),'') = u.email_address
and u.email_opt_out_fl <> 'Y'
)
group by e.SOCIALSEC, COALESCE(ltrim(rtrim(e.EMAIL1)), ltrim(rtrim(e.EMAIL2)), ltrim(rtrim(e.EMAIL3)),'')
order by 2 desc
WHERE NOT EXISTS
(
SELECT *
FROM dbo.EMAIL_UNSUBSCRIBE_LIST u
WHERE COALESCE(ltrim(rtrim(e.EMAIL1)), ltrim(rtrim(e.EMAIL2)), ltrim(rtrim(e.EMAIL3)),'') = u.email_address
and u.email_opt_out_fl <> 'Y'
)
AND COALESCE(ltrim(rtrim(e.EMAIL1)), ltrim(rtrim(e.EMAIL2)), ltrim(rtrim(e.EMAIL3)),'') <> ''