declare @ownerId int = ; declare @ownerType varchar(3) = ; with explicitMembership as ( select crm.orgId, crm.affiliationType, crm.sortRank, crm.partnerId as operatorId, crm.affiliationId, AffOpr.companyName, AffOpr.fsl_choId, AffOpr.fsl_choId as oneFSId from tbl_CRM_Affiliations crm with (nolock) inner join tbl_OPR_ClientOperators AffOpr with (nolock) on AffOpr.operatorId = crm.orgId and AffOpr.ownerId = crm.ownerId and AffOpr.fsltablecode = crm.ownerType where crm.ownerId = @ownerId and crm.ownerType = @ownerType and crm.partnerType = 'OPR' and crm.affiliationStatus = 'A' ) , implicitMembership as ( select crm.orgId, crm.affiliationType, crm.ranking as sortRank, crm.partnerId as operatorId, cast (null as int) affiliationId, AffOpr.companyName, AffOpr.fsl_choId, AffOpr.fsl_choId as oneFSId from tbl_CRM_InferredAffiliations crm with (nolock) inner join tbl_OPR_ClientOperators AffOpr with (nolock) on AffOpr.operatorId = crm.orgId and AffOpr.ownerId = crm.ownerId and AffOpr.fsltablecode = crm.ownerType where crm.ownerId = @ownerId and crm.ownerType = @ownerType and crm.partnerType = 'OPR' and not exists ( select * from explicitMembership xm with (nolock) where xm.orgId = crm.orgId and xm.affiliationType = crm.affiliationType and xm.operatorId = crm.partnerId ) ) , data as ( select * from explicitMembership with (nolock) union all select * from implicitMembership with (nolock) ) , data as ( select * from explicitMembership with (nolock) ) #forJSON( 'data', attributes.dataStore, 'companyName' )#