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

Question

Hi Gurus,

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.Thanks in advance.

Answer 1

don't user an inner join, use a left or right join, depending which values from which tables you want.

so:

SELECT *
FROM tasks t
LEFT JOIN taskentries te
ON t.id = te.id

which is the same statement as:

SELECT *
FROM tasksentries te
RIGHT JOIN tasks t
ON te.id = t.id

will get you all tasks, even if there is no taskentry

an inner join will only select rows when there are rows in both tables, left join selects all rows from the left (first) table and matching from the other row (if there is no such row, null will be the value of all columns). right join will do the oposite: select all rows from right (second) table and matching from left.

a LEFT JOIN b is the same as b RIGHT JOIN a

Answer 2

Hi all,

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.