with territoriesAndBrokers as ( select distinct ter.name as territoryName, ter.territoryId, ter.territoryPath, coalesce( sma.primaryOffice, 'N' ) territoryPrimaryOffice, b.broker_name brokerName, b.broker_id brokerId, bo.broker_office_id as brokerOfficeId, bo.broker_office_name as brokerOfficeName from tbl_TER_Territories ter with (nolock) inner join tbl_SMA_BrokerOfficeLink sma with (nolock) on sma.territoryId = ter.territoryId and sma.mfr_id = ter.ownerId inner join tblBrokerOffices bo with (nolock) on bo.broker_office_id = sma.brokerOfficeId inner join tblBrokers b with (nolock) on b.broker_id = bo.broker_id where ter.ownerId = and ter.fsl_tablecode = ) #forJSON( 'territoriesAndBrokers', attributes.dataStore, 'brokerName' )#