What I want to retrieve is how many hours have been assigned to a project and category (which is in the Manhours table) and how much time has been actually used (which is is the Tasks table)
So if I look at the data I can see that ChargeNum 111111, catid 19 has a total of 104 hours assigned in the Manhours table (80 for EmployeeId=200020 and 24 for EmployeeId=200003) Then if I look at the Tasks table I see that ChargeNum 1111111, Catid 19 has a total of 36 hours (this are the hours that have been actually used) What I would like as a final result is something like this Catid Hours Used Hours Alloted 19 | 36 |104 -----Original Message----- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 03, 2004 9:05 AM To: Mysql (E-mail) Subject: Re: Problem with joins and query ----- Original Message ----- From: "Luis Lebron" <[EMAIL PROTECTED]> > Select M.Catid, sum(M.Hours) as allotedhours, sum(T.Hours) as usedHours > >From Manhours as M, Tasks as T > Where M.ProjectId=32 > AND T.ChargeNum=M.ChargeNum > AND T.EmployeeID=M.EmployeeID > AND T.Catid=M.Catid > Group by M.Catid > Order by M.Catid It might be more understandable if you read it like: SELECT M.Catid, SUM(M.Hours) AS allotedhours, SUM(T.Hours) AS usedHours FROM Manhours AS M INNER JOIN Tasks as T USING (ChargeNum, EmployeeID, Catid) WHERE M.ProjectId=32 GROUP BY M.Catid Is it correct that the two tables are joined on three columns? Maybe it's best to try and refrase how the data in the two tables is connected and what exactly you want to retrieve. Try to think in data: "for project 32 I want all the records from table a plus the records from table b which have the same value for col_name, sum the values in col_name and sort them by col_name". From this you will be able to construct your query easily... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
