# Boolean check on fields returning any of them except if all have same value

Assuming we have 4 boolean fields a,b,c,d

How can we return any of the fields that are true, but not nothing when all 4 are true?

`where a=true or b=true or c=true or d=true`

will return a row where even all 4 fields are set tot true. How can I exclude this row from the query?

Same would apply if searched value is `false`

• Extra point: it does not matter if you search for `true` or `false`. The answer (query and result) are the same ;) Commented Jun 2, 2015 at 17:50

You can use something like this:

``````where (true in (a,b,c,d)
and (a,b,c,d) <> (true,true,true,true));
``````

Which can be simplified (suggested by ypercubeᵀᴹ) to:

``````where true in (a,b,c,d) and false in (a,b,c,d)
``````

Note that it will not handle NULL values properly. If you want that as well, you will need to use `coalesce()`.

This is a direct translation of "not when all 4 are true" into SQL:

``````where NOT(a=true AND b=true AND c=true AND d=true)
``````

Edit:

@a_horse_with_no_name shortened it further to

``````where NOT(a AND b AND c AND d)
``````

Edit2:

This doesn't work correctly (will return a row when all four columns are false), so mixing with @a_horse_with_no_name's answer:

``````where NOT(a AND b AND c AND d) AND (a OR b OR c OR d)
``````

Another way using Postgres aggregate functions that I thought would work but actually doesn't!
(don't try this):

``````where bool_or(a, b, c, d) and not bool_and(a, b, c, d)
``````

This will work but is much more verbose than the other answers:

``````where exists
(select 1
from (values (a), (b), (c), (d)) as b(i)
having bool_or(i) and not bool_and(i)
)
``````

(somewhat simplified):

``````where (select bool_or(i) and not bool_and(i)
from (values (a), (b), (c), (d)) as b(i)
)
``````

This however, works. It's based on `FALSE < TRUE` ordering:

``````where greatest(a,b,c,d) = true and least(a,b,c,d) = false
``````

or:

``````where greatest(a,b,c,d) and not least(a,b,c,d)
``````

or just:

``````where greatest(a,b,c,d) > least(a,b,c,d)
``````