select distinct dst.dstId, dst.dstCompanyType from tbl_CDR_DIstributors cdr with (nolock) inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_recordId = select d.dstId, d.dstName, d.dstAddr1, d.dstAddr2, d.dstCity, rtrim( d.dstState ) dstState, rtrim( d.dstZip ) dstZip, d.dstCompanyType, p.dstId parentDstId, p.dstName parentDstName, '#_col#:#_dstId#' distributorKeyId from tbl_DST_Distributors d with (nolock) left outer join tbl_DST_Distributors p with (nolock) on p.dstId = d.dstParentCo where d.dstId = for json path, include_null_values