Advanced Mysql query with case and when

Question

I'm working in one scenario where I need to display few records from the table in which id does not exist in the list.

SELECT * FROM contract 
WHERE `id` NOT IN ( 
    CASE 
        WHEN type = 1 THEN '159,154' 
        WHEN type = 2 THEN '' 
        WHEN type = 3 THEN '' 
    END 
) 
ORDER BY id DESC

Over here you can see that if contract type = 1 then the result should not display 159 & 154 records from the table. But 154 number record is also coming in the result which should not.

Any help appreciated!

`'159,154'` != `'159','154'` – Lukasz Szozda Jun 05 '18 at 15:21 — Lukasz Szozda, Jun 05 '18 at 15:21
thanks @lad2025, So how can i achieve that ? – Pratik Patel Jun 05 '18 at 15:23 — Pratik Patel, Jun 05 '18 at 15:23

score 1 · Answer 1 · answered Jun 05 '18 at 15:34

1

...WHERE (type = 1 AND id NOT IN('159','154')) 
OR   (type = 2 AND id !='') 
OR   (type = 3 AND id !='')

Remove the quotes if the id is numeric so you can use a possible index.

answered Jun 05 '18 at 15:34

Mihai

26,325
7
66
81

cdaiga · Answer 2 · 2018-06-05T15:45:08.683

0

Use MySQL INSTR() function as shown below:

SELECT * FROM contract 
WHERE INSTR(IF(type = 1, '159,154',''),`id`)<0
ORDER BY id DESC;

edited Jun 05 '18 at 15:45

answered Jun 05 '18 at 15:28

cdaiga

4,861
3
22
42

I just converted the case to if, try again. – cdaiga Jun 05 '18 at 15:45

score 0 · Answer 3 · answered Jun 05 '18 at 15:38

0

I think this would work:

SELECT * FROM contract 
WHERE (type = 1 AND id NOT IN ('159','154')) OR
    (type IN (2,3) AND id != '')
ORDER BY id DESC

answered Jun 05 '18 at 15:38

A. Colonna

852
7
10

Advanced Mysql query with case and when

3 Answers3