Select field from multiple Fields

Transact-SQL
1

I would like to return only 2 fields but one of the fields is coming from 3 fields.

Select  e.uniqueid, e.email
from  dbo.email e,
         dbo.unsubscribe u
where e.email is not null
and  e.email = u.email
and u.email_unsubscribe = 'Y'

field (email): if email1 is not null then email1, if email2 is not null then email2, if email3 is not null then email3 else ''

I am looking to only pull one email per identifier that is not on the unsubscribe list. Email1 should always be used first then email2 then email3

2

I assume you need to use the COALESCE() function for your requirement.

3

Something like this?

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'
);
4

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
5

Add to the WHERE clause

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)),'') <> ''