declare @ownerId int; declare @ownerType varchar(3); set @ownerId = ; set @ownerType = ; with territoriesAndDistributors as ( SELECT distinct cdr.cdr_dstcode, cdr.fsl_tablecode, cdr.ownerid, cdr.cdr_recordid as distId, cdr.cdr_territoryId, cdr.cdr_dstname as distName, cdr.cdr_dstPath, cdr.cdr_recordId, cdr.cdr_dstName, cdr.cdr_dstcity, cdr.cdr_dststate, cdr.focusAccount, cdr.cdr_dstcompanyType, cdr.cdr_dstAddress1, cdr.cdr_dstAddress2, cdr.cdr_dstZip, cdr.comments, cdr.classificationId, cdr.primaryContactId, cdr.fsl_dstId, ---> pcdr.cdr_dstname parent_cdr_dstName, pcdr.cdr_recordId parent_cdr_recordId, pcdr.cdr_dstcompanytype parent_cdr_dstcompanytype, ---> cdr.mfr_bsr_id, cdr.mfr_bsr_id2, cdr.setMethod, ter.territoryId, ter.name territoryName, ter.territoryPath, cdr.recordSource, cdr.foodServiceRewardsId, cdr.cdr_dstPhone, cdr.cdr_dstFax, cdr.cdr_priority, cdr.hasSysFields, cdr.useSysFields, cdr.sys_dstname, cdr.sys_dstcity, cdr.sys_dststate, cdr.sys_dstAddress1, cdr.sys_dstAddress2, cdr.sys_dstZip,cdr.sys_dstPhone, cdr.sys_dstFax, cdr.gs1_gln, c.Description, cdr.crmActive FROM tbl_CDR_Distributors cdr with (nolock) inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = cdr.cdr_territoryId and ter.ownerId = cdr.ownerId and ter.fsl_tablecode = cdr.fsl_tablecode ---> ---> #_join# tbl_DST_Distributors bdst with (nolock) on bdst.dstid = cdr.fsl_dstid #_join# tbl_DST_Distributors pdst with (nolock) on pdst.dstid = bdst.dstparentco #_join# tbl_CDR_Distributors pcdr with (nolock) on pcdr.fsl_dstid = pdst.dstid and pcdr.ownerId = cdr.ownerId and pcdr.fsl_tablecode = cdr.fsl_tablecode LEFT JOIN dbo.tbl_CDR_Classifications C with (nolock) ON C.classificationId = cdr.classificationId AND c.ownerid = cdr.ownerid AND c.ownertype = cdr.fsl_tablecode WHERE cdr.fsl_tablecode = @ownerType AND cdr.ownerid = @ownerId -- AND cdr.crmactive = 'Y' /* only show distributors, not buying groups */ AND ( cdr.cdr_dstcompanytype in ( 'P', 'B' ) or cdr.cdr_dstcompanytype is null )---> ) #forJSON( 'territoriesAndDistributors', attributes.dataStore, 'cdr_dstPath' )#