SELECT DISTINCT cdr.cdr_recordId ,COALESCE(cdr.cdr_dstCode,'') as 'cdr_dstCode' ,cdr.cdr_dstName /* ,cdr.cdr_dstCompanyType AS 'companyType' ,coalesce(dst.dstCompanyType,'?') AS 'ofsCompanyType' */ FROM tbl_CDR_Distributors cdr with (nolock) LEFT JOIN tbl_DST_Distributors dst with (nolock) ON cdr.FSL_dstid = dst.dstid WHERE cdr.ownerId = AND cdr.fsl_tablecode = ANDcdr.cdr_territoryId = ORDER BY cdr_dstname FOR json path, include_null_values