SELECT r.requestId, c.assignedContractNbr, c.contractName ,case when c.partnerType = 'OPR' then opr.companyName when c.partnerType = 'CDR' then cdr.cdr_dstname end as partnerName FROM tbl_TPM_Contracts c with (nolock) INNER JOIN tbl_TPM_Requests r with (nolock) ON r.contractId = c.contractId AND r.ownerID = c.ownerId AND r.ownerType = c.ownerType LEFT OUTER JOIN tbl_OPR_ClientOperators opr with (nolock) ON opr.operatorId = c.partnerId AND opr.ownerId = c.ownerId AND opr.fslTableCode = c.ownerType AND c.partnerType = 'OPR' LEFT OUTER JOIN tbl_CDR_Distributors cdr with (nolock) ON cdr.cdr_recordId = c.partnerId AND cdr.ownerId = c.ownerId AND cdr.fsl_TableCode = c.ownerType AND c.partnerType = 'CDR' WHERE c.ownerID = AND c.ownerType = ORDER BY partnerName asc FOR json path, include_null_values