Bring all records if parameter is different

Question

Bring all records if parameter is different

Navigation

#1 by (3 votes)
#2 by (1 votes)

3

I have a contrato table with the concluido field of type tinyint(1) , if it is 0 it is not complete and if it is 1 it is complete. I'm going to pass this value by parameter to the query, how do I get all the data when the value of this parameter is different from 0 and 1?

sql query

asked by anonymous 07.12.2016 / 20:27

2 answers

1

Use "coalesce" (default on all sql banks) to save an "or" if your parameter is null too.

SELECT * From contract ONDE completed =: parameter or coalesce (: parameter, -1) not in (0, 1);

Another way would be this:

SELECT * From contract ONDE completed = case when coalesce (: parameter, -1) between 0 and 1 then: parameter else completed end

09.12.2016 / 04:16

Interface that defines the generic signature of the methods to be implemented How to install a java application with MySQL database?

score 3 · Accepted Answer

Use the OR clause for conditional search.

SELECT c.*
  FROM contrato c
 WHERE :parametro NOT IN (0, 1)
    OR c.concluido = :parametro;

If the parameter can be null, use one more clause OR :

SELECT c.*
  FROM contrato c
 WHERE :parametro IS NULL
    OR :parametro NOT IN (0, 1)
    OR c.concluido = :parametro;