declare @ownerId int = ; declare @ownerType varchar(3) = ; with referenceDistClients as ( select distinct m.mfr_id , m.mfr_name as companyname from tblManufacturers m with (nolock) where m.mfr_id in ( select distinct cdr.ownerId from tbl_CDR_Distributors cdr with (nolock) where cdr.fsl_tablecode = 'MFR' and exists ( select * from tbl_CDR_Distributors cdr1 with (nolock) where cdr1.fsl_dstId = cdr.fsl_dstId and cdr1.ownerId = @ownerId and cdr1.fsl_tablecode = @ownerType ) and cdr.fsl_dstId > 0 ) ), distributorClients as ( select mfr_id as mfrId, companyname from referenceDistClients ) #forJSON( 'distributorClients', attributes.datastore, 'mfrId' )#