使用或在where子句中为零或为null

Question

I have a query, i want to display the result if two of my fields are equal at zero or null.

foreach ($query=$db->query("SELECT *,
t1.CMD_ID, t1.CMD_CLT_RS, t1.CMD_CLT_ID,
t2.CLT_ID, t2.CLT_CODE
FROM sous_commandes 
LEFT OUTER JOIN commandes AS t1 ON (t1.CMD_ID=sous_commandes.SCMD_CMD_ID)
LEFT OUTER JOIN client AS t2 ON (t2.CLT_ID=t1.CMD_CLT_ID)
WHERE SCMD_FRN_ID = '$frn_id' AND SCMD_FACT_TOTAL_HT = '0' || IS NULL AND SCMD_FACT_TOTAL_TTC = '0' || IS NULL
ORDER BY STR_TO_DATE(SCMD_DATE, '%d/%m/%Y') DESC") as $donnees):

I tried to use || IS NULL but it doesn't work. No result are display. Thanks.

Answer 1

change

SCMD_FACT_TOTAL_HT = '0' || IS NULL

to

(SCMD_FACT_TOTAL_HT = '0' OR  SCMD_FACT_TOTAL_HT  IS NULL)

same for SCMD_FACT_TOTAL_TTC of course.

EDIT

You could also do

COALESCE(SCMD_TOTAL_HT, '0') = '0'