MySQL SELECT Query – Using WHERE and CASE Statements

MySQL

I do not really know if this is possible to do this but I will expose my problem.

I have two tables cases and progress

cases

+----------+--------------+---------------------+---------+------+
| id_cases |     name     |    date_surgery     | archive | done |
+----------+--------------+---------------------+---------+------+
|        1 | Cranioplasty | 2016-02-01 00:00:00 |       1 |    0 |
|        2 | Cranioplasty | 2016-02-02 00:00:00 |       0 |    0 |
|        3 | Other        | 2016-02-03 00:00:00 |       0 |    0 |
|        4 | Osteotomy    | 2016-02-04 00:00:00 |       0 |    0 |
|        5 | Bone Tumor   | 2016-02-05 00:00:00 |       1 |    1 |
+----------+--------------+---------------------+---------+------+

progress (which contains thousands of records)

+-------------+---------+---------+---------+
| id_progress | task_id | case_id | current |
+-------------+---------+---------+---------+
|           1 |  103006 |       1 |       0 |
|           2 |  103002 |       1 |       1 |
|           3 |  103003 |       1 |       1 |
|           4 |  201006 |       5 |       0 |
|           5 |  201007 |       5 |       1 |
|        .... |     ... |     ... |      ...|
+-------------+---------+---------+---------+

The link between the tables is cases.id_cases = progress.case_id

I want to select all cases with archive and done = 0. I also want to get some progress that are linked to this case

I tought about a condition to select a specific range of task_id related to the result of cases.name.

So basically I want this

SELECT id_cases, name, date_surgery, task_id, current
FROM cases 
LEFT JOIN progress on progress.case_id = cases.id_cases 
WHERE archive = 0 AND done = 0

But if name is Cranioplasty I just want progress.task_id that are equal to 103006, 103002. For Bone Tumor I want 201006 and 205003. And this for all the different name.

There is no logic between the id_cases and the task_id. I must hardcode this.

I tried differents things but none suceeded

SELECT id_Cases, name, date_surgery, task_id, current
from cases 
left join progress on progress.case_id = cases.id_cases 
where archive = 0 and done = 0 
and case when name='Cranioplasty' then task_id=103006 and task_id=103002 else
case when name='Bone Tumor' then task_id=201006 else
case when name='Osteotomy' then task_id=301002 else
case when name='MBIO' then task_id=401006 end end end end 
order by name, date_surgery

In fine I try to get this result (task_id is not important, I just want to result of current)

+------+--------------+-----------+--------+-------+--------+-------+--------+-------+
| case |     name     | date_surg | task_1 | 1_res | task_2 | 2_res | task_3 | 3_res |
+------+--------------+-----------+--------+-------+--------+-------+--------+-------+
| 1    | Cranioplasty | date      | 103006 |     0 | 103002 |     0 | 105002 |     1 |
| 1    | Cranioplasty | date      | 103006 |     1 | 103002 |     1 | 105002 |     0 |
| 1    | Cranioplasty | date      | 103006 |     1 | 103002 |     0 | 105002 |     1 |
| 2    | Cranioplasty | date      | 103006 |     0 | 103002 |     1 | 105002 |     0 |
| 2    | Cranioplasty | date      | 103006 |     1 | 103002 |     0 | 105002 |     1 |
| 2    | Cranioplasty | date      | 103006 |     0 | 103002 |     1 | 105002 |     1 |
| 3    | Bone Tumor   | date      | 201006 |     1 | 205003 |     0 | 205005 |     0 |
| 3    | Bone Tumor   | date      | 201006 |     0 | 205003 |     1 | 205005 |     1 |
| ...  |              |           |        |       |        |       |        |       |
+------+--------------+-----------+--------+-------+--------+-------+--------+-------+

Best Answer

You have several problems with the query you listed:

  1. 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)
  2. 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.
  3. 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).
  4. 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;
Related Question