Mysql – Subquery check for every occurence – Gantt style

MySQLsubquery

I am storing a sort of gantt chart into my MySQL database and want to retrieve the "current" tasks for each "case". One case is translated as one project.

The schema will help a lot so here is the core content

The main table with static tasks

+---------------+
|     tasks     |
+---------------+
| id | name     |
+----+----------+
| 1  | step n°1 |
+----+----------+
| 2  | step n°2 |
+----+----------+
| 3  | step n°3 |
+----+----------+
| 4  | step n°4 |
+----+----------+

The relations between tasks.
Here, task n°4 can be achieved only if task n°2 and task n°3 are done.

+-------------------------+
|    tasks_predecessor    |
+-------------------------+
| id | task | predecessor |
+----+------+-------------+
| .. | 2    | 1           |
+----+------+-------------+
| .. | 3    | 1           |
+----+------+-------------+
| .. | 4    | 2           |
+----+------+-------------+
| .. | 4    | 3           |
+----+------+-------------+

And the progress table where the state of each step is recorded for each "case".

Current = 0 when the task is done. 
Current = 1 mean the task is still active
+-------------------------------+
|            progress           |
+-------------------------------+
| id | task_id | case | current |
+----+---------+------+---------+
| .. |    1    |  199 |    0    |
+----+---------+------+---------+
| .. |    2    |  199 |    0    |
+----+---------+------+---------+
| .. |    3    |  199 |    1    |
+----+---------+------+---------+
| .. |    4    |  199 |    1    |
+----+---------+------+---------+
| .. |    1    |  200 |    1    |
+----+---------+------+---------+
| .. |    2    |  200 |    0    |
+----+---------+------+---------+
| .. |    3    |  200 |    0    |
+----+---------+------+---------+
| .. |    4    |  200 |    0    |
+----+---------+------+---------+

I want to retrieve the current "do-able" tasks, I mean without the tasks that need a predecessor which is not already done.

For the case n°199, I want to retrieve

+----------+---------------+
|  t.name  | p.id_progress |
+----------+---------------+
| step n°3 |       ..      |
+----------+---------------+

Because step n°4 need task n°2 and task n°3. But only task n°2 is accomplished. The query should return the task n°4 of case n°199 only if task n°1,2 and 3 are done.

I already tried this kind of query

SELECT t.name as 't.name', p.id_progress as 'p.id_progress' 
FROM task_predecessor tp 
LEFT JOIN task t ON t.id_task = tp.task
LEFT JOIN progress p ON p.task_id = t.id_task
WHERE tp.predecessor IN (SELECT task_id FROM progress WHERE case_id = 199 AND current = 0)
AND current = 1
AND p.case_id = 199
GROUP BY p.id_progress

I know that these part WHERE tp.predecessor IN (SELECT task_id FROM progress WHERE case_id = 199 AND current = 0) will not work. But I have no idea if this is possible (and how ?) to retrieve what I want in a single query without procedure

Best Answer

I might derive the progress table into two pieces... one with the zeros (complete) and one with the ones (not yet done), and join them together by the case number.

Then I'd join those with the predecessor table to find rows where the task is in the ones table and it's predecessor is in the zeros table.

Once I've identified those tasks, I'd join with the main table to get the task's proper name.

That might look something like this:

SELECT
    t.name,
    p1.id_progress
FROM
    tasks_predecessor tp
INNER JOIN
    (SELECT task_id,case,id FROM progress where current = 0) p0
    ON p0.task_id = tp.predecessor
INNER JOIN
    (SELECT task_id,case,id FROM progress where current = 1) p1
    ON p1.task_id = tp.task
    AND p0.case = p1.case
INNER JOIN
    tasks t
    ON t.id = tp.task

Then, if you wanted a particular case, you could isolate it with:

WHERE p0.case = 199

Give this a try, but don't hold me to it... I'm not in front of a console where I can test this, and I may have missed something obvious or simple.

Related Solutions

MySQL 5.6 – Aggregating Sum Day by Day from Multiple Joins Returns Unexpected Results

I first thought that you needed to GROUP BY the datetable in a derived table as well, to avoid a cross join but it seems that the datetable already has distinct dates so that is not the cause. The problem is the <= in the (more than one) joins. This creates a kind of cross join and wrong results.

So, the solution is to do the LEFT JOIN and the <= inside the derived tables and in the external level join with equality = and no GROUP BY:

Another issue comes from not using GROUP BY DATE(DateColumn). Use that - and then the ON conditions can be simplified as well.

I also changed the DATE(tx.created) <= DATE(dt.thedate) condition to tx.created < (dt.thedate + INTERVAL 1 DAY) so an index on (created, hours) can be used:

SELECT
DATE_FORMAT(dt.thedate, '%Y-%m-%d') AS the_date,
COALESCE(tt.sum_task, 0) AS sum_t,
COALESCE(tg.sum_group, 0) AS sum_g

FROM datetable AS dt

LEFT JOIN   (                                  -- this could be an INNER JOIN, no difference
                SELECT
                dt.thedate,
                SUM(tx.hours) as sum_task

                FROM datetable AS dt
                  LEFT JOIN tasks AS tx
                    ON tx.created < (dt.thedate + INTERVAL 1 DAY)

                 -- Some extra where clauses here   -- move them to the ON above

                GROUP BY dt.thedate
            )
            AS tt ON tt.thedate = dt.thedate

LEFT JOIN   (                                 -- this could be an INNER JOIN, no difference
                SELECT
                dt.thedate,
                SUM(gx.hours) as sum_group

                FROM datetable AS dt
                  LEFT JOIN groups AS gx
                    ON gx.created < (dt.thedate + INTERVAL 1 DAY)

                 -- Some extra where clauses here   -- move them to the ON above

                GROUP BY dt.thedate
            )
            AS tg ON tg.thedate = dt.thedate

ORDER BY dt.thedate ;

Tested at SQLFiddle

MySQL SELECT Query – Using WHERE and CASE Statements

You have several problems with the query you listed:

In the first test, you use and AND instead of an OR or an IN (I perfer IN -- in fact, I would prefer it in all cases so that it is easy to extend later, but that is your choice)
You are nesting your case statements. You only want a single case statement, with multiple conditions. What you have is basically a nested if statement.
You never terminated the last case statement with an ELSE clause (I use ELSE 0 below, but in some cases, ELSE 1=0 OR ELSE FALSE might be needed to render a boolean value -- ELSE NULL should work too).
You have a LEFT JOIN that is converted into a JOIN because of the WHERE clause... I think that is what you actually want, but you should double check

The query should look more like this:

SELECT id_Cases, name, date_surgery, task_id, current
FROM cases
JOIN progress ON progress.case_id = cases.id_cases
WHERE archive = 0
  AND done = 0
  AND CASE name
    WHEN 'Cranioplasty' THEN task_id IN (103006, 103002) 
    WHEN 'Bone Tumor' THEN task_id = 201006 
    WHEN 'Osteotomy' THEN task_id = 301002 
    WHEN 'MBIO' THEN task_id = 401006 
    ELSE 0
  END
ORDER BY name, date_surgery;

I think this is what you want. If you really want the LEFT JOIN, it would look like this:

SELECT id_Cases, name, date_surgery, task_id, current
FROM cases
LEFT JOIN progress ON progress.case_id = cases.id_cases
    AND CASE name
      WHEN 'Cranioplasty' THEN task_id IN (103006, 103002) 
      WHEN 'Bone Tumor' THEN task_id = 201006 
      WHEN 'Osteotomy' THEN task_id = 301002 
      WHEN 'MBIO' THEN task_id = 401006 
      ELSE 0
    END
WHERE archive = 0
  AND done = 0
ORDER BY name, date_surgery;

Best Answer

Related Solutions

MySQL 5.6 – Aggregating Sum Day by Day from Multiple Joins Returns Unexpected Results

MySQL SELECT Query – Using WHERE and CASE Statements

Related Question