Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
|
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 17th, 2009, 12:46 PM
Authorized User
 
Join Date: Nov 2007
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
Default joining three tables

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 |
+------+------------+------------+---------------+------+------+------+------+
 
Old March 18th, 2009, 12:48 AM
Friend of Wrox
 
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

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.
The Following User Says Thank You to urtrivedi For This Useful Post:
nasirmunir (March 23rd, 2009)
 
Old March 18th, 2009, 08:53 AM
Authorized User
 
Join Date: Nov 2007
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
Default

thanks a lot, my friend.
My test database was inconsistent, and the query is working perfect !!!
 
Old March 18th, 2009, 02:31 PM
Authorized User
 
Join Date: Nov 2007
Posts: 54
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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 ;





Similar Threads
Thread Thread Starter Forum Replies Last Post
Help joining tables and restricting records bren582 SQL Server 2005 3 January 18th, 2008 02:45 PM
joining tables pit_bait SQL Language 2 September 28th, 2007 07:29 AM
Joining Three Tables Alexpizzoferro Classic ASP Databases 5 September 1st, 2006 04:08 PM
joining 3 tables koskalgr MySQL 5 March 24th, 2006 09:52 AM
Joining two tables rajustha SQL Server 2000 1 December 7th, 2003 07:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.