| SQL |
SELECT DISTINCT fsp.FSPro_UserID AS PKID, fsp.FirstName, fsp.LastName,
fsp.Title, fsp.Phone, fsp.email, fsp.PhoneExt, bo.BROKER_AREACODE, bo.BROKER_PHONE_NUMBER, bo.Broker_Office_Name AS Office,
bo.HeadquarterYesNo, bol.BrokerOfficeID, bro.broker_name, bro.broker_id
from tbl_SMA_BrokerOfficeLink bol with (nolock)
inner join tblMfrBrokerLink mbl with (nolock)
on mbl.broker_office_id = bol.brokerofficeid
and mbl.mfr_id = 1237
inner join tbl_ORG_AttributeLinks oal with (nolock)
on oal.child_attrID = mbl.broker_office_id
and oal.child_attrTypeID = 27
and oal.mstr_attrTypeID = 34
inner join tbl_fspro_members fsp with (nolock)
on fsp.fspro_userid = oal.mstr_attrID
and fsp.ownerid = bol.brokerid
and fsp.fsl_tablecode = 'bro'
and fsp.PrimaryOfficeID in ( select broker_office_id from tblbrokeroffices where broker_id = bol.brokerid )
inner join tblBrokerOffices bo with (nolock)
on bo.broker_office_id = mbl.broker_office_id
inner join tblmanufacturers mfr with (nolock)
on mfr.mfr_id = bol.mfr_id
and mfr.FseManagedAccess in (1,2,3)
inner join tblbrokers bro with (nolock)
on bro.broker_id = bol.brokerid
inner join tbl_sec_memgrplink mgl with (nolock)
on mgl.FSPro_UserID = fsp.FSPro_UserID
inner join tbl_SEC_Groups grp with (nolock)
on grp.GroupID = mgl.GroupID
where bol.mfr_id = 1237
and bol.brokerid = 387
AND ( select count(*)
from tbl_FSPro_MembersCoExclude
where FSPro_Userid = fsp.FSPro_UserID
and CoID = 1237
and CoType = 'MFR') = 0
and (
(
fsp.firstname like ? or
fsp.lastname like ? or
bro.broker_name like ?
)
)
ORDER BY HeadquarterYesNo desc, Office, LastName asc |