with referenceClientsList as ( select distinct mfr.mfr_id mfrId, mfr.mfr_name as companyname from tbl_OPR_CLientOperators opr with (nolock) inner join tblManufacturers mfr with (nolock) on mfr.mfr_id = opr.ownerId where opr.fsltablecode = 'MFR' and opr.fsl_choId = and exists ( select * from tbl_CRM_Affiliations a with (nolock) where a.orgId = opr.operatorId and a.orgType = 'OPR' and a.ownerId = opr.ownerId and a.ownerType = opr.fsltablecode and nullif( a.partnerAffiliateNbr, '' ) is not null ) ) #forJSON( 'referenceClientsList', attributes.dataStore, 'mfrId' )#