Google Sheets Query - True/False/Other

Question

I'm trying to get a query based on a tri-state. The possible values are TRUE, FALSE, 'Ignore' or blank. I want this to select all names (B) and emails (C) where a flag column is set to False or blank.

=QUERY(Roster!B3:T, "SELECT B,C WHERE M<>TRUE AND M<>'IGNORE'")

I've also tried

=QUERY(Roster!B3:T, "SELECT B,C WHERE M=FALSE OR M=''")

and

=QUERY(Roster!B3:T, "SELECT B,C WHERE M=FALSE OR M IS NULL")

but none gives me all the people who don't have TRUE or Ignore, and do have FALSE or blank.

Is there a way to actually do TRUE/FALSE/OTHER column query conditions, or is this an impossible task?

Answer 1

Google Query Language inferes the column data type and the values that are not of the inferred data type are ignored.

TRUE and FALSE are booleans but OTHER is string. Depending on the order of the values of one type or the other are being ignored.

Answer 2

while regular =OR =AND returns booleans, OR AND in =QUERY is different, so in other words almost anything is possible:

=QUERY(Roster!B3:T; "select B, C where M ='"&"FALSE"&"' OR M ='"&""&"'"; 0)

Answer 3

Try with:

QUERY(A8:F16,"SELECT * WHERE A = "&TRUE()&" ",1)