with claimPartners as ( select distinct c.ownerId, c.ownerType, c.partnerId, c.partnerType from tbl_TPM_Claims c with (nolock) inner join tbl_TPM_Contracts ct with (nolock) on ct.ownerId = c.ownerId and ct.ownerType = c.ownerType and ct.partnerId = c.partnerId and ct.partnerType = c.partnerType where c.ownerId = and c.ownerType = ) select opr.companyName, c.partnerId, c.partnerType from claimPartners c inner join tbl_OPR_CLientOperators opr with (nolock) on opr.operatorId = c.partnerId and opr.ownerId = c.ownerId and opr.fsltablecode = c.ownerType where c.partnerType = 'OPR' union all select cdr.cdr_dstName as companyName, c.partnerId, c.partnerType from claimPartners c inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = c.partnerId and cdr.ownerId = c.ownerId and cdr.fsl_tablecode = c.ownerType where c.partnerType = 'CDR' order by partnerType, companyName for json auto, include_null_values