with branchDistributors as ( SELECT cdr.cdr_recordId, cdr.cdr_dstName, d.dstId, pcdr.cdr_recordId as parentRecordId FROM tbl_CDR_Distributors cdr with (nolock) INNER JOIN tbl_DST_Distributors d with (nolock) ON cdr.FSL_dstid = d.dstid and cdr.cdr_dstcompanyType = d.dstcompanyType inner JOIN tbl_DST_Distributors pd with (nolock) on pd.dstid = d.dstparentco inner join tbl_CDR_Distributors pcdr with (nolock) on pcdr.cdr_dstcompanyType = pd.dstcompanyType and pcdr.FSL_dstid = pd.dstid WHERE cdr.OwnerID = AND cdr.FSL_TableCode = AND d.dstCompanyType = 'B' and pd.dstcompanyType = 'P' ) #forJSON( 'branchDistributors', attributes.dataStore, 'cdr_dstName' )#