How to combine these two selects

[zWaR]

Hello,

i'm working on a project which includes a special kind of search. Till now i programmed it using two selects (connecting them together with help of php):

SELECT pro.*, mem.id, mem.name, mem.login, mem.email_work, mem2.id, mem2.name, mem2.login, mem2.email_work, tas.project, tas.owner, tas.assigned_to, tas.id, tas.worked_hours, tas.task_rate, tas.worked_hours*tas.task_rate AS earned, tas.start_date, tas.due_date, tas.complete_date
FROM projects pro
LEFT OUTER JOIN tasks tas ON tas.project = pro.id
LEFT OUTER JOIN members mem ON mem.id=tas.owner
LEFT OUTER JOIN members mem2 ON mem2.id=tas.assigned_to WHERE ( tas.assigned_to IN(10)) GROUP BY mem2.id,pro.id ORDER BY pro.id ASC

and

SET @worked=0, @earned=0;
SELECT IF (tas.worked_hours IS NOT NULL, @worked:=@worked+worked_hours,@worked) AS ure, IF (tas.worked_hours IS NOT NULL, @earned:=tas.task_rate+@earned, @earned) AS earned
FROM tasks tas
LEFT OUTER JOIN projects pro ON pro.id=tas.project
LEFT OUTER JOIN members mem ON mem.id=tas.assigned_to
WHERE pro.id IN (25) AND tas.assigned_to IN (10) ORDER BY ure DESC LIMIT 1;

Now let me expain the idea behind. The project is a php-based application which holds information about workers, which have assigned different tasks and each task is included to one project. So there are many projects, each of them has many tasks binded. Each task, binded to a particular project is assigned to one worker, which works on it. Each task has it's rate (how much money does a worker get for completing it, independent on time he/she spend's on the task). What i want to accieve is to program a search, which will display some data, among which there's also worked hours and earnings for particular project.

For example if John works on 2 projects named proj1 and pro2, the search must display his earnings for each project and how much hours of work he spent on each of them (be aware, earnings and worked hours are actually binded to tasks). Till now i accomplished that with usage of two selects (pasted above). First one selects all the data besides worked hours and earnings for all projects a worker is working on, the second calculates earnings and worked hours for one worker and for one project he's working on.

Now the problem, i must create one select out of this two, which would return all the data of the first select, together with worked hours and earnings calculation for a particular user. This means if i'm doing a search for worker John i'd like this result: all projects he is working on, for each project how many hours he spent and for each project how much money he earned (worked hours and earnings are binded to tasks!) beside that also other data, which is not related to this problem.

Yes, i know it's hard to understand and it's even harder to describe the problem, so please ask for additional explanation if there's something unclear.

 

[zWaR]

I forgot to mention something, the application i'm working on is called phpCollab (

http://www.php-collab.org/blog/).

I'm writing some additional add-ons for the application (i'm not a member of their programmers crew).