Hi Gurus,
I have three tables
Tasks
with columnsTaskid, Taskname
TaskAllocations
with columnsTaskid, EmpNum
TaskEntries
with columnsTaskId, EmpNum, WorkedDate, Hoursspent
Now I want to get all the task entries along a particular week. Here my problem is even if there is no Taskentry for a particular task I should get atleast a row with that TaskId, and Taskname with Hoursspent as Null in the query's resultset. I have been trying to get this with the below query.
SELECT A.TaskId,
B.TaskName,
SUM( C.HoursSpent ) as TotalHours ,
C.WorkedDate, C.Comments
FROM TaskAllocations A
LEFT OUTER JOIN TaskEntries C
ON A.TaskId = C.TaskId
AND A.EmpNum = C.EmpNum
INNER JOIN Tasks B
ON A.TaskId = B.TaskId
WHERE A.EmpNum =123456
AND C.WorkedDate
IN ('2010-01-17','2010-01-18','2010-01-19',
'2010-01-20','2010-01-21','2010-01-22','2010-01-23' )
GROUP BY A.TaskId, C.WorkedDate
ORDER BY A.TaskId,C.WorkedDate ASC ';
What I am getting for this SQL piece is if and only if there is an entry for a particular task id, then only i am getting a row for that. but what I want is to get atleast a row for each and every task that is available to a EmpNum. Even if I get one row for each TaskId and WorkedDate combination no issues. Please help me with this. Actual intention of this is to build a HTML two dimensional table with each task entry against date and task as shown below.
--------------------------------------------------------- TaskId TaskName Sun Mon Tue Wed Thu Fri Sat --------------------------------------------------------- 18 name1 2 3 4:30 3:30 19 name2 20 name3 4 2:30 22 name4 2:30 23 name5 24 name6 1:30 6 ---------------------------------------------------------
So that this can be updated by the user for each year week. First I thought of group_concat but because of performance I am using normal group by query.
Note: for a particular taskid and workeddate there will be only one entry of hoursspent. I have almost built the frontend. Please help me to get all task ids as above even if there is no entry. Do I need to use subquery.Thanks in advance.