I think you want it this way, using a CASE statement for the leadmgr
column. I also moved all your subselects into left joins, which should
give a bit better performance.
<cfquery datasource="askseaton" name="getOffices" result="varibles">
select
s.address + ' ' + s.address2 + ' ' + s.address3 + ',' + s.city + ',' +
s.state + ',' + s.city as office_address,
leadmgr = case
when (lm.user_id is null) then lm.lead_note
else lm.first + ' ' + lm.last
end,
amr.first + ' ' + amr.last as director,
sn.first + ' ' + sn.last as SDIRNDIR,
vp.first + ' ' + vp.last as VICEPRE,
fm.first + ' ' + fm.last as FASTMGR
from sourcebook_1 s left outer join
user_info lm on s.lead_mgr = lm.user_id left outer join
user_info amr on s.area_mgr_rdo = amr.user_id left outer join
user_info sn on s.sdir_ndir = sn.user_id left outer join
user_info vp on s.vice_pre = vp.user_id left outer join
user_info fm on s.fast_mgr = fm.user_id
where active = 1
and office_id not in (36,37,38,73)
order by office_number
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324792
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
