Shadow Fiend Shadow Fiend - 1 year ago 96
MySQL Question

MYSQL Inner Join with Criteria

I have a MySQL Command and it looks like this

UPDATE Variance as VAR INNER JOIN receiving as REV ON (VAR.ItemCode = REV.ItemCode)
SET VAR.Receiving = REV.QtyPack * REV.PCS + REV.QtyStan;

and its working perfectly.
now my question here is how can I apply this criteria to mysql command?

1.Receiving.Date is Between 2 Dates

2.Receiving.Status = "Posted"

3.If Receiving.QtyPack and Receiving.QtyStan = "0" or "NULL" then Var.Receiving will become "0"

I hope you dont downvote this.
Please do not hesitate to ask question or clarification and I will edit my post

Answer Source

I think you can just add the conditions directly into the query:

       receiving REV
       ON VAR.ItemCode = REV.ItemCode
    SET VAR.Receiving = COALESCE(REV.QtyPack * REV.PCS + REV.QtyStan, 0)
    WHERE rev.Status = 'Posted' AND
 between $date1 and $date2;

The COALESCE() converts NULL values to 0, which is part of the third condition.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download