CODEWITHSUNDEEP
mysqlif-statementwhere-clause
cattarantadoughan
cattarantadoughan

Reputation: 509

MySQL where statement based on if

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

Upvotes: 3

Views: 14467

Answers (3)

Nathan TM
Nathan TM

Reputation: 143

This works too

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

Upvotes: 2

sagi
sagi

Reputation: 40481

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)

Upvotes: 5

1000111
1000111

Reputation: 13519

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.

enter image description here

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

Upvotes: 5

Related Questions