Search code examples
mysqlsqlselectwhere-clause

SQL questions of WHERE / CASE

I have a problem on sql statment. It has the selection box to choose parameter value, include 'A', 'B' and Empty. They will pass to :ABC value

How can I handle when select 'A', the data will show result related 'A', B will select B and empty will select both A and B.

I have no idea to handle when select "Empty", the sql will select both A and B. Thank you so much for helping me.

Select * From table WHERE table.abc IN
(CASE WHEN :ABC = 'A' THEN 'A'
WHEN :ABC = 'B' THEN 'B'
WHEN :ABC IS NULL THEN ('A','B')
END);```
Solution

  • Using IN SELECT..., in stead of IN (..) can solve this:

    Select * 
From table 
WHERE table.abc IN
   (SELECT 'A' WHERE :ABC='A' or :ABC IS NULL
    union
    SELECT 'B' WHERE :ABC='B' or :ABC IS NULL
   )