CODEWITHSUNDEEP
mysqlsql
Pello
Pello

Reputation: 93

How to build an WHERE clause with IF statement with value OR IS NULL

It seems to be tricky to use the WHERE clause if you want to filter on an attribute value that is a value or is NULL.

This is for Retool (a dynamic MySQL environment). I've tried to split it up in different AND clauses but it seems to be harder than it looks because a value needs an equation, and IS NULL doesn't.

SELECT *
FROM table
WHERE filter = IF({{checkbox1.value}} = TRUE,0,1 OR IS NULL) 
ORDER BY order DESC

What I need a query that delivers a list based on the state of the checkbox:

If checkbox = true, I want to select records where the filter value is 0

If checkbox = false, I want to select records where the filter value is 1 or null

Upvotes: 2

Views: 1308

Answers (4)

Paul Spiegel
Paul Spiegel

Reputation: 31772

If checkbox = true, I want to select records where the filter value is 0

If checkbox = false, I want to select records where the filter value is 1 or null

Almost literally translated in code:

WHERE
  CASE 
    WHEN {{checkbox1.value}} = TRUE  THEN filter = 0
    WHEN {{checkbox1.value}} = FALSE THEN filter = 1 OR filter IS NULL
  END

That can be shortened to

WHERE
  CASE {{checkbox1.value}}
    WHEN TRUE  THEN filter = 0
    WHEN FALSE THEN COALESCE(filter, 1) = 1
  END

But since in MySQL TRUE and FALSE are just aliases for 1 and 0, you can also express the same logic with

WHERE COALESCE(filter, 1) = (NOT {{checkbox1.value}})

or 

WHERE COALESCE(filter, 1) = (1 - {{checkbox1.value}})

Upvotes: 0

forpas
forpas

Reputation: 164089

It is not clear what you check to be null, I guess you mean filter:

SELECT *
FROM table
WHERE 
  ({{checkbox1.value}} AND filter = 0) 
  OR 
  ((NOT {{checkbox1.value}}) AND coalesce(filter, 1) = 1))
ORDER BY order DESC

Upvotes: 2

fahim152
fahim152

Reputation: 2629

in MySQL , IF statements in query works like this

IF(condition, value_if_true, value_if_false)

whereas 2nd and 3rd parameter is optional and first one is mandatory .

So answer might be

SELECT * FROM table WHERE filter = IF({{checkbox1.value}} = TRUE,0,1) AND IS NULL ORDER BY order DESC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Not really. You can just use OR:

WHERE (filter = {{checkbox1.value}} OR {{checkbox1.value}} IS NULL)

Upvotes: 0

Related Questions