If tag from subtask empty, inherit tag from parent task

Question

I have two tables, and i would like to get the sum of the logged minutes for the tasks grouped by tag, most of the time the subtasks have the same tag as the parent but in some cases, they don't.

How can i get the sum of logged minutes grouped by tag, including the subtasks so if they don't have a tag they inherit the tag from their parent.

Task

+-----+-----------+------+-------+------------+
| id  | parent-id | name |  tag  | is-subtask |
+-----+-----------+------+-------+------------+
| 001 |           | a    | sales |          0 |
| 002 |       001 | b    | sales |          1 |
| 003 |       001 | c    |       |          1 |
| 004 |           | d    | store |          0 |
+-----+-----------+------+-------+------------+

times

+-----+---------+-------------+----------------+
| id  | task-id | description | logged-minutes |
+-----+---------+-------------+----------------+
| 991 |     001 | Time for a  |             15 |
| 992 |     002 | Time for ab |             60 |
| 993 |     002 | Time for ab |             75 |
| 994 |     003 | Time for ac |             35 |
| 995 |     004 | Time for d  |             20 |
+-----+---------+-------------+----------------+

score 0 · Accepted Answer · edited May 23 '17 at 10:28

Basically you need to use COALESCE() function which returns first non-null value found in the list (if there are only null values, it evaluates to null).

After you got the info about tags from the derived table (inner part of the query) you may then join that information to times table to calculate a sum for each tag.

I have not tested the code, but I believe it should work - this assumes your relation is only 1 level deep. If you have a deeper hierarchy, check out How to create a MySQL hierarchical recursive query for some tweaks to this code.

SELECT
    alltasks.tag
  , SUM( times.logged-minutes ) AS logged-minutes
FROM (
    -- take parent tasks
    SELECT
        tparent.id
      , tparent.tag
    FROM task tparent
    UNION
    -- take child tasks
    SELECT
        tchild.id
      , COALESCE( tchild.tag, tparent.tag ) -- if no child tag, use parent tag
    FROM task tparent
    INNER JOIN task tchild ON 
      tparent.id = tchild.parent-id
      AND tchild.is-subtask = 1 -- may not be necessary unless you have different relationships
    ) alltasks
LEFT JOIN times ON 
  alltasks.id = times.task-id
GROUP BY alltasks.tag

If tag from subtask empty, inherit tag from parent task

1 Answers1