select cho_zipCode from tbl_CHO_Operators with (nolock) where cho_id = select brokerId, brokerOfficeId from tbl_SMA_BrokerOfficeLink with (nolock) where mfr_id = and ( territoryId = and territoryId > 0 ) select distinct ter.ownerId, ter.fsl_tablecode, ter.territoryId, ter.name as territoryName from tbl_BRO_Principals p with (nolock) inner join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.principalId = p.principalId and pt.ownerId = p.ownerId and pt.ownerType = p.ownerType inner join tbl_BRO_PrincipalTerritoryResources pto with (nolock) on pto.ownerId = pt.ownerId and pto.ownerType = pt.ownertype and pto.principalTerritoryId = pt.principalTerritoryId and pto.resourceType = 'BO' inner join tbl_BRO_PrincipalTerritoryResources ptt with (nolock) on ptt.ownerId = pto.ownerId and ptt.ownerType = pto.ownertype and ptt.principalTerritoryId = pto.principalTerritoryId and ptt.resourceType = 'TER' inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = ptt.ownerId and ter.fsl_tablecode = ptt.ownerType and ter.territoryId = ptt.resourceId where p.ownerId = and p.mfrId = and pto.resourceId = select opr.ownerId, opr.fsltablecode, opr.operatorId, opr.companyName, opr.city, opr.state, opr.zipCode, opr.fsl_choId, opr.mfr_bsr_id, opr.territoryId from tbl_OPR_CLientOperators opr with (nolock) where opr.fsltablecode = 'BRO' and opr.ownerId = and opr.territoryId in ( ) and ( opr.fsl_choId = or ( coalesce( opr.fsl_choId, 0 ) = 0 and exists ( select * from tbl_OPR_CLientOperators opr2 with (nolock) inner join tbl_ORG_AttributeLinks oal with (nolock) on oal.mstr_attrTypeId = 41 and oal.child_attrTypeId = 41 and ( oal.child_attrId = opr.operatorId or oal.mstr_attrId = opr.operatorId ) where opr2.ownerId = opr.ownerId and opr2.fsltablecode = opr.fsltablecode and opr2.fsl_choId = ))) with relevantOperators as ( select opr.ownerId, opr.fsltablecode, opr.operatorId, opr.companyName, opr.city, opr.state, opr.zipCode, opr.fsl_choId, opr.mfr_bsr_id from tbl_OPR_CLientOperators opr with (nolock) where opr.fsltablecode = 'BRO' and opr.ownerId = and opr.territoryId in ( ) and ( opr.fsl_choId = or ( coalesce( opr.fsl_choId, 0 ) = 0 and exists ( select * from tbl_OPR_CLientOperators opr2 with (nolock) inner join tbl_ORG_AttributeLinks oal with (nolock) on oal.mstr_attrTypeId = 41 and oal.child_attrTypeId = 41 and ( oal.child_attrId = opr.operatorId or oal.mstr_attrId = opr.operatorId ) where opr2.ownerId = opr.ownerId and opr2.fsltablecode = opr.fsltablecode and opr2.fsl_choId = ))) ) select distinct opr.ownerId, opr.fsltablecode, opr.operatorId, opr.territoryId, ter.name as territoryName, opr.fsl_choId, opr.mfr_bsr_id, mem.email, mem.fspro_userId, mem.title, mem.firstname, mem.lastName, img.ImgPath + '\' + img.ImgName + img.ImgExt as contactPhoto, logo.image as logo from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = opr.territoryId and ter.ownerId = opr.ownerId and ter.fsl_tablecode = opr.fsltablecode inner join tblMfrBrokerLink link with (nolock) on link.broker_id = opr.ownerId and link.mfr_id = inner join tblBrokerOffices bo with (nolock) on bo.broker_office_id = link.broker_office_id and bo.broker_id = link.broker_id inner join tbl_FSPRO_Members mem with (nolock) on mem.ownerId = opr.ownerId and mem.fsl_tablecode = opr.fsltablecode and mem.fspro_userId = opr.mfr_bsr_id left outer join qry_ContentLookup img with (nolock) on img.contentId = mem.imageId inner join tblBrokers bro with (nolock) on bro.broker_id = opr.ownerId left outer join qryImageFullPathLookup logo with (nolock) on logo.imageId = bro.brokerCompanyLogo inner join tbl_ORG_AttributeLinks oal with (nolock) on oal.child_attrTypeId = 27 and oal.child_attrId = bo.broker_office_id and oal.mstr_attrTypeId = 34 and oal.mstr_attrId = mem.fspro_userId where opr.fsltablecode = 'BRO' and opr.ownerId = and bo.broker_office_id = and bo.broker_office_id in ( ) and opr.operatorId in ( select operatorId from relevantOperators )