I am trying to display project name, estimated time and time spent on all projects (time entries has time spent for all projects, and tasks has estimated time for all projects).
I am joining projects with time entries to get the project name and sum of time spent on each project. Then I am joining tasks and time entries to get the estimated time on tasks for each project. I am struggling a bit wit that, can I get some direction?

My query and result:

mysql> select p.project_name, sum(te.time_spent), sum(t.estimated_time)
-> from projects p
-> left join time_entries te on te.pid=p.pid
-> left join tasks t on t.tid=te.tid
-> group by p.project_name ;
+---------------+--------------------+-----------------------+
| project_name | sum(te.time_spent) | sum(t.estimated_time) |
+---------------+--------------------+-----------------------+
| Time tracking | 90 | 75 |
| Wiki Set-up | 45 | 55 |
+---------------+--------------------+-----------------------+

time spent values are fine, but estimated time is wrong (55), should be 25.

My tables:
projects
+-----+---------------+-------------+
| pid | project_name | url |
+-----+---------------+-------------+
| 1 | Time tracking | www.abc.com |
| 2 | Wiki Set-up | www.abc.com |
+-----+---------------+-------------+

tasks
+-----+---------------+----------------+------+
| tid | task_name | estimated_time | pid |
+-----+---------------+----------------+------+
| 1 | Design | 55 | 1 |
| 2 | Testing | 20 | 1 |
| 3 | Testing | 10 | 2 |
| 4 | Specification | 15 | 2 |
+-----+---------------+----------------+------+

time_entries
+------+------------+------------+---------------+------+------+------+------+
| teid | date | time_spent | comments | aid | tid | uid | pid |
+------+------------+------------+---------------+------+------+------+------+
| 1 | 2009-03-16 | 45 | test comments | 1 | 1 | 1 | 1 |
| 2 | 2009-03-16 | 45 | test comments | 2 | 2 | 2 | 1 |
| 3 | 2009-03-16 | 45 | test comments | 1 | 1 | 1 | 2 |
+------+------------+------------+---------------+------+------+------+------+

In oracle query is execting fine. You just check the syntax for subquery in mysql

select p.project_name, sum(t.estimated_time) ,te.time_spent
from projects p
left join tasks t on t.pid=p.pid
left join ( select pid, sum(time_spent) time_spent from time_entries group by pid ) te on te.pid=p.pid
group by p.project_name,te.time_spent
order by p.project_name

__________________
urt

Help yourself by helping someone.

thanks a lot, my friend.
My test database was inconsistent, and the query is working perfect !!!

another possible work around:

select p.project_name,
(select sum(te.time_spent) from time_entries te where te.pid=p.pid) as total_time_spent,
(select sum(t.estimated_time) from tasks t where t.pid=p.pid) as total_time_estimated
from projects p
group by p.project_name ;