CODEWITHSUNDEEP
mysql
Atnaize
Atnaize

Reputation: 1816

MySQL query with WHERE and CASE

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 and 105002. For Bone Tumor I want 201006, 205003 and 207001. And this for each 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 |
| ...  |              |           |        |       |        |       |        |       |
+------+--------------+-----------+--------+-------+--------+-------+--------+-------+

PS : I just put my table as an example to help understanding the problem. It does not includes all the records

I know that I can use temporary or virtual table but I wanted to know how to resolve this with only a query

Upvotes: 0

Views: 82

Answers (1)

Bisk
Bisk

Reputation: 36

Well, anyway its bad practice to hardcode this relations between name and task id. You need to store them in database or smthng. So you can join that table and do it in 1 query.

If you cant or dont want to its better to save them atleast to an array so you can dynamically generate query part with this conditions if its possible. 


    select
            a.id_cases,
            a.name,
            b.task_id,
            b.current
        from
            cases a
        left join
            progress b ON b.case_id = a.id_cases
        where
            a.archive = 0
            and a.done = 0
            and (
                (b.task_id in (103006,103002,105002) and a.name = 'Cranioplasty')
                OR (b.task_id in (201006,205003,207001) and a.name = 'Bone Tumor')
            )

If you can use any language to generate this part


    and (
            (b.task_id in (103006,103002,105002) and a.name = 'Cranioplasty')
                OR (b.task_id in (201006,205003,207001) and a.name = 'Bone Tumor')
        )

Do it. For example in php


    foreach($arRealtions as $name => $taskIDs)
    {
        $query .= '(b.task_id in ('.implode(',',$taskIDs).') and a.name = "'.$name.'")';
    }

Upvotes: 1

Related Questions