declare @ownerId int = ; declare @ownerType varchar(3) = ; with brokerMfrClients as ( select distinct 'MFR' ownerType, mfr.mfr_id mfrId, mfr.mfr_name as companyname from tbl_BRO_Principals bpl with (nolock) inner join tblManufacturers mfr with (nolock) on mfr.mfr_id = bpl.mfrId where bpl.ownerType = @ownerType and bpl.ownerId = @ownerId and exists ( select * from tbl_BRO_PrincipalTerritories pt with (nolock) where pt.ownerId = bpl.ownerId and pt.ownerType = bpl.ownerType and pt.principalId = bpl.principalId and pt.hireStatus = 'A' ) union select 'MFR' ownerType, mfr.mfr_id mfrId, mfr.mfr_name as companyname from tblManufacturers mfr where exists ( select * from tbl_IMPORT_Aliases a with (nolock) where a.ownerId = mfr.mfr_id and a.ownerType = 'MFR' and a.objectType = 'OPR' and a.alias like ) ) #forJSON( 'brokerMfrClients', attributes.datastore, 'mfrId' )#