declare @ownerId int = ; declare @ownerType varchar(3) = ; with allCompetitors as ( select distinct mc.mfr_name as competitorName, pc.competitorId as competitorId, p.mfrId as mfr_id from tbl_BRO_PrincipalCompetitors pc with (nolock) inner join tbl_BRO_Principals p with (nolock) on p.ownerId = pc.ownerId and p.ownerType = pc.ownerType and p.principalId = pc.principalId inner join tblManufacturers mc with (nolock) on mc.mfr_id = pc.mfrid and mc.crmActive = 'Y' where pc.ownerId = @ownerId and pc.ownerType = @ownerType select distinct companyName as competitorName, competitorId, ownerId as mfr_id from tbl_CMP_Companies with (nolock) where ownerId = @ownerId and ownerType = @ownerType ) #forJSON( 'allCompetitors', attributes.dataStore, 'competitorName' )#