Hi,
I have the below stated query, and as you can see the part
select userId from users where companyId IN (11, 37)
occures twice. Is there a SQL or MySQL way of referencing this query the first time so I only need to refer to the reference the second time? Obviously the way I am using it causes unneeded overhead as the query is executed twice.
The full query:
---------------------
SELECT * FROM
(SELECT
projects.*,
sum(timeentries.hoursEntered) as hours,
sum(timeentries.moneySpent) as timeMoney,
sum(expenseentries.moneySpent) as expenseMoney
FROM
projects,
phases
LEFT JOIN
expenseEntries
ON
phases.phaseId=expenseentries.phaseId AND
expenseentries.entryBy IN (select userId from users where companyId IN (11, 37))
LEFT JOIN
timeentries
ON
phases.phaseId=timeentries.phaseId AND
timeentries.entryBy IN (select userId from users where companyId IN (11, 37))
WHERE
projects.projectId=phases.projectId
GROUP BY
projects.projectId
ORDER BY
projects.projectName
) as result
WHERE
result.timeMoney>0 OR result.expenseMoney>0;
----------------------
Any comments would be appreciated.
Regards,
Joost Schouten
CEO - JS Portal
http://www.jsportal.com