Help newbe optimize query

[contiw]

The following query returns the expected recordset but all those subqueries look weird to me.
Is there a way of optimizing it? Thank You

<cfquery name="session.getJobs" datasource="#db#">
SELECT
j.*,
IIF([status] = 'completed','b','a') as statusorder,
j.duration*u.BasicFees as charges,
(select userLastName+' '+userFirstName+', '+userTitle from users where userId = j.providerId) as provName,
(select userCity from users where userId = j.providerId) as provCity,
(select userstateProv from users where userId = j.providerId) as provState,
(select Company from users where userId = j.providerId) as provLocation,
(select userAddress1 from users where userId = j.providerId) as provAddress,
(select userZipcode from users where userId = j.providerId) as provZip,
(select userPhone from users where userId = j.providerId) as provPhone
from JobsDB j, users u
WHERE j.clientID = #cookie.newClientId#
AND u.userId = #cookie.newClientId#
order by j.jobID asc
</cfquery>

 

[PHV]

Have you tried this ?
SELECT
j.*,
IIF([status] = 'completed','b','a') as statusorder,
j.duration*u.BasicFees as charges,
v.userLastName+' '+v.userFirstName+', '+v.userTitle as provName,
v.userCity as provCity,
v.userstateProv as provState,
v.Company as provLocation,
v.userAddress1 as provAddress,
v.userZipcode as provZip,
v.userPhone as provPhone
from JobsDB j, users u, users v
WHERE j.clientID = #cookie.newClientId#
AND u.userId = #cookie.newClientId#
AND v.userId = j.providerId
order by j.jobID asc

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[contiw]

Like a charm PH. Good lesson. Thank You.