Enjoy coding Enjoy coding -4 years ago 84
MySQL Question

Mysql scenario - Get all tasks even if there is no entry?

I have three tables


  1. Tasks
    with columns
    Taskid, Taskname

  2. TaskAllocations
    with columns
    Taskid, EmpNum

  3. TaskEntries
    with columns
    TaskId, 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.

Answer Source

After rigorous testing of different options I came up with the below solution which will give the required results for me.

SELECT Final.TaskId, 
       Final.TaskName, 
       Tmp.HoursSpent AS TotalHours, 
       Tmp.WorkedDate
    FROM (
        SELECT A.TaskId, B.TaskName, A.EmpNum
        FROM TaskAllocations A
                 INNER JOIN 
                 Tasks B 
            ON ( A.TaskId = B.TaskId ) 
        WHERE a.empnum = "333"
        )Final
    LEFT OUTER JOIN (
            SELECT New.TaskId, New.EmpNum, New.WorkedDate, New.HoursSpent
        FROM TaskEntries New
        WHERE New.WorkedDate
        IN 
              ('2010-01-17','2010-01-18','2010-01-19',
              '2010-01-20','2010-01-21','2010-01-22','2010-01-23' )
        OR New.WorkedDate IS NULL 
        AND New.EmpNum = "333"
        )Tmp 
    ON Tmp.TaskId = Final.TaskId
AND Tmp.EmpNum = Final.EmpNum
    ORDER BY Final.TaskId, Tmp.WorkedDate ASC ;

The first query of mine in the question was not working as I was putting a condition on right table's column while doing Left Outer Join. Thanks to all for the support.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download