select fsp.ownerid, fsp.fsl_tablecode ownertype, bro.BrokerAbbrevName from tbl_fspro_members fsp with (nolock) left outer join tblbrokers bro with (nolock) on bro.broker_id = fsp.ownerid and fsp.fsl_tablecode = 'bro' where fspro_userid = select case when opp.partnertype = 'opr' then opr.address else cdr.cdr_dstaddress1 end as address, case when opp.partnertype = 'opr' then opr.city + ' ' + opr.state + ', ' + opr.zipcode else cdr.cdr_dstcity + ' ' + cdr.cdr_dststate + ', ' + cdr.cdr_dstzip end as citystatezip from tbl_spl_opportunities opp with (nolock) left outer join tbl_opr_clientoperators opr with (nolock) on opr.OperatorID = opp.partnerid and opp.partnertype = 'opr' left outer join tbl_cdr_distributors cdr with (nolock) on cdr.cdr_recordid = opp.partnerid and opp.partnertype = 'cdr' where opp.ownerid = and opp.ownertype = and opp.opportunityid = select skuid from tbl_prd_skus with (nolock) where ownerid = and fsl_tablecode = and sku =