MySQL where statement based on if

Question

Hi I'm making an MySQL statement from the following logic.

Select * from content 
if(description=='educational')
where serial like '%EDU2015%'
else where serial like '%NOVEL2015%'

The logic is simply as, if the description column is educational, the filter should have the string "EDU2015. Else, it should filter with the serial of "NOVEL2015"

I'm not sure if im using the way to use the IF statement in mysql but i dont know where to place the where statements from https://dev.mysql.com/doc/refman/5.7/en/if.html

try case :dev.mysql.com/doc/refman/5.0/en/case.html

Mikey
– Mikey

2016-03-13 10:44:45 +00:00
Commented Mar 13, 2016 at 10:44 — Mikey
– Mikey, Commented Mar 13, 2016 at 10:44
try case and divide your logic into cases

Uzumaki Naruto
– Uzumaki Naruto

2016-03-13 10:45:49 +00:00
Commented Mar 13, 2016 at 10:45 — Uzumaki Naruto
– Uzumaki Naruto, Commented Mar 13, 2016 at 10:45

sagi · Accepted Answer · 2016-03-13 10:55:36Z

5

You can use CASE expression like this:

SELECT * FROM content
WHERE serial like CASE WHEN description = 'educational'
                       THEN '%EDU2015%'
                       ELSE '%NOVEL2015%'
                  END

Note that when you compare values, a single equal sign is required and not two.

EDIT: If you want to filter on different columns based on a condition, you can use this:

SELECT * FROM content
WHERE (description = 'educational' and  serial like '%EDU2015%')
    OR(description <> 'educational' and id>100)

edited Mar 13, 2016 at 10:55

answered Mar 13, 2016 at 10:45

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sagi Over a year ago

@UzumakiNaruto Actually CASE is ANSI SQL, so I think all versions support it

cattarantadoughan Over a year ago

Thanks! it works. But what if i need a different filter for different condition? Let's say. Select * from content if(description=='educational') where serial like '%EDU2015%' else where id >100

1000111 · Accepted Answer · 2016-03-13 10:48:32Z

5

You can use IF in WHERE block like that:

Select * 
from content 
where serial like IF(description = 'educational','%EDU2015%','%NOVEL2015%');

Alternatively you can choose CASE WHEN expression like @sagi stated.

mysql> SELECT IF(1>3,'true','false');
+------------------------+
| IF(1>3,'true','false') |
+------------------------+
| false                  | 
+------------------------+
1 row in set (0.00 sec)

answered Mar 13, 2016 at 10:48

1000111

13.5k2 gold badges32 silver badges43 bronze badges

Comments

Nathan TM · Accepted Answer · 2022-07-05 08:29:11Z

2

This works too

Select * from content 
where if(description = 'educational', serial like '%EDU2015%', serial like '%NOVEL2015%');

answered Jul 5, 2022 at 8:29

Nathan TM

1431 silver badge6 bronze badges

Collectives™ on Stack Overflow

MySQL where statement based on if

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related