declare @ownerId int = ; declare @ownerType varchar(3) = ; with mfrs as ( select mfr_id, mfr_name from tblManufacturers with (nolock) where mfr_id = @ownerId select distinct mfr.mfr_id, mfr.mfr_name from tbl_BRO_Principals p with (nolock) inner join tblManufacturers mfr with (nolock) on mfr.mfr_id = p.mfrId inner join tbl_BRO_PrincipalTerritories pt (nolock) on pt.principalId = p.principalId and pt.ownerId = p.ownerId and pt.ownerType = p.ownerType inner join tbl_BRO_PrincipalTerritoryResources ptr with (nolock) on ptr.ownerId = pt.ownerId and ptr.ownerType = pt.ownerType and ptr.principalTerritoryId = pt.principalTerritoryId and ptr.resourceType = 'TER' and ptr.resourceId in ( ) where p.ownerId= @ownerId and p.ownerType = @ownerType and p.crmActive = 'Y' and pt.hireStatus = 'A' ) #forJSON( 'mfrs', attributes.datastore, 'mfr_name' )#