How do I fix, and possibly simplify I guess, this SQL query, please?

SELECT * FROM table WHERE
   M IN (NULL,1) AND T IN (NULL,1) AND J IN (NULL,1) AND B IN (NULL,1)
   AND (ISNULL(M,0) ISNULL(T,0) ISNULL(J,0) ISNULL(B,0))<4
   ORDER BY (ISNULL(M,0) ISNULL(T,0) ISNULL(J,0) ISNULL(B,0)) DESC

table contains 4 columns (M,T,J,B) with 3 possibles values only NULL, 0, 1.

• First-line filters to get only entries containing NULL or 1.
• Second-line makes an addition to get the total of M J T B and filters only if <4.
• Third-line ORDER BY the same Total, M J T B.

error #1582 - Incorrect parameter count in the call to native function 'ISNULL'

MySQL equivalent to ISNULL is IFNULL... answered here

CodePudding user response：

Untested of course but the following might be what you are after

select * from (
    select *, coalesce(M,0) M2, coalesce(T,0) T2, coalesce(J,0) J2, coalesce(B,0) B2
    from table
    where 
     (M is null or M=1) and
     (T is null or T=1) and
     (J is null or J=1) and
     (B is null or B=1) 
)x
where M2 T2 J2 B2 < 4
order by M2 T2 J2 B2 desc

CodePudding user response：

Nulls act strangely in SQL. Any comparison test with a null will result in FALSE, so if you use the phrase Null=Null in a where clause, nothing will be retrieved because the result is always false. I would rewrite your second line to use IsNull functions instead,

IsNull(M,1)*IsNull(T,1)*IsNull(J,1)*IsNull(B,1)>0

CodePudding user response：

Select 
 t.*,
 coalesce(M,0)   coalesce(T,0)   coalesce(J,0)   coalesce(B,0) as calc
from table t
where 
 coalesce(M,0) in (0,1) and
 coalesce(T,0) in (0,1) and
 coalesce(J,0) in (0,1) and
 coalesce(B,0) in (0,1) and
 calc < 4
order by calc desc