declare @ownerId int = ; declare @ownerType varchar(3) = ; with mfrEmailDomains as ( select d.ownerId, d.ownerType, d.emailDomain, d.ready, case when exists ( select * from tblMfrBrokerLink mbl where mbl.broker_id = bd.ownerId and mbl.mfr_id = d.ownerId ) then 'Y' else 'N' end as brokerSide, case when exists ( select * from tbl_SMA_BrokerOfficeLink sma where sma.brokerid = bd.ownerId and sma.mfr_id = d.ownerId ) then 'Y' else 'N' end as mfrSide, bd.ready brokerReady from tbl_WRK_EmailDomains d with (nolock) left outer join tbl_WRK_EmailDomains bd with (nolock) on bd.ownerType = 'BRO' and bd.emailDomain = d.emailDomain and bd.corporate = 'Y' where d.ownerType = 'MFR' and d.ownerId > 0 ), broEmailDomains as ( select d.ownerId, d.ownerType, d.emailDomain, d.corporate, d.ready, md.ready as mfrReady, case when exists ( select * from tblMfrBrokerLink mbl where mbl.broker_id = d.ownerId and mbl.mfr_id = md.ownerId ) then 'Y' else 'N' end as brokerSide, case when exists ( select * from tbl_SMA_BrokerOfficeLink sma where sma.brokerid = d.ownerId and sma.mfr_id = md.ownerId ) then 'Y' else 'N' end mfrSide from tbl_WRK_EmailDomains d left outer join tbl_WRK_EmailDomains md on md.ownerType = 'MFR' and md.emailDomain = d.emailDomain and md.corporate = 'Y' where d.ownerType = 'BRO' and d.ownerId > 0 ), otherEmailDomains as ( select ownerId, ownerType, emailDomain, ready from tbl_WRK_EmailDomains with (nolock) where ownerType not in ( 'MFR', 'BRO' ) and ownerId > 0 ), emailDomains as ( select ownerId, ownerType, emailDomain, case when mfrReady is null then ready when brokerSide = 'Y' and mfrSide = 'Y' then mfrReady else 'N' end as ready from broEmailDomains union all select ownerId, ownerType, emailDomain, case when brokerReady is null then ready when brokerSide = 'Y' and mfrSide = 'Y' then brokerReady else 'N' end as ready from mfrEmailDomains union all select ownerId, ownerType, emailDomain, ready from otherEmailDomains ), emailSendersRaw as ( select ownerId, ownerType, emailDomain from emailDomains where ready in ( 'Y', '?' ) and emailDomain in ( select emailDomain from tbl_WRK_EmailDomains where ownerId = @ownerId and ownerType = @ownerType and corporate = 'Y' ) and not ( ownerId = @ownerId and ownerType = @ownerType ) ), emailSenders as ( select mfr.mfr_name allowedSender, esr.emailDomain from emailSendersRaw esr inner join tblManufacturers mfr with (nolock) on mfr.mfr_id = esr.ownerId where esr.ownerType = 'MFR' union all select b.broker_name allowedSender, esr.emailDomain from emailSendersRaw esr inner join tblBrokers b with (nolock) on b.broker_id = esr.ownerId where esr.ownerType = 'BRO' union all select ownerType + ':' + cast( ownerId as varchar ) as allowedSender, esr.emailDomain from emailSendersRaw esr where esr.ownerType not in ( 'BRO', 'MFR' ) ) #forJSON( 'emailSenders', attributes.datastore, 'emailDomain' )#