I am trying to grab email address from different sources for selected Account IDs. I have all email address in Contact table those are grabbed from Account and Opportunity table. So task is first I need to look for Email address in contact table based on Account IDs if Email found then grab else look for other source which is Opportunity if found grab it.
So priority is first grab email where source is "Account" if email is blank for Account then go and look for source as "Opportunity"
I tried this query, it successfully bring all emails associated with Accountd IDs where source is Account.
select t1.uuid, t1.accountid as account_id, t2.email_address, t2.source, t2.store_name
from mpfmaster2 t1
outer apply
(select TOP 1 t2.*
from global_contact_table_master t2
where t1.accountid = t2.account_id
and t2.source in ('sfdc_account_primary', 'sfdc_account',)) t2
But the problem is how Do i run the other query to grab email address where Email address is blank. I tried the below query, It does brings in email address of only missing AccountID's. I wanted to bring in all emails from above Query and then check if email is blank then go search for source as Opportunity?
select t1.uuid, t1.account_id, t2.email_address, t2.source, t2.store_name
from mpf_v2_account_primary t1
outer apply
(select TOP 1 t2.*
from global_contact_table_master t2
where t1.account_id = t2.account_id
and t2.source in ('sfdc_opportunity')) t2
where t1.email_address is null
Any idea how do i do this?
