Subquery check for every occurence - Gantt style 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
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.