How To Convert Null Value to Zero On LEFT JOIN Query

I got stuck on my software development process on how to show items that are not completely ordered to sub-contractor. The SQL query result always shows only items that some are already ordered but not yet complete. I need this to warn the production department as well as marketing department.

This article tells how you can easily convert NULL value using MySQL COALESCE function.

I have order table like this:

SELECT p.code AS product_code, pd.qty AS buyer_qty_order, wd.qty AS qty_ordered_to_sub_con FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
WHERE lpp.id = ‘1′

Buyer Order Quantity

Since I have ordered some items to sub contractor, I need to know how many quantity has been ordered.

SELECT p.code AS product_code, pd.qty AS buyer_qty_order,
(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) AS qty_ordered_to_sub_con FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
LEFT JOIN wo_detail wd ON wd.pi_detail_id=pd.id
WHERE lpp.id = ‘1′
GROUP BY pd.id

Sub Contractor Ordered Quantity

NULL value on AU-01A means that this item is not yet ordered to sub contractor.

I also need to know the balance.

SELECT p.code AS product_code, pd.qty AS buyer_qty_order,
(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) AS qty_ordered_to_sub_con,
(pd.qty-(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id)) AS qty_balance FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
LEFT JOIN wo_detail wd ON wd.pi_detail_id=pd.id
WHERE lpp.id = ‘1′
GROUP BY pd.id

Balance

AU-01A has NULL value on both last two columns.

Since I need to eliminate item that is completely ordered to sub contractor, I execute this query and the result is not like what I want. The query only returns items which is ordered but not all.

SELECT p.code AS product_code, pd.qty AS buyer_qty_order,
(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) AS qty_ordered_to_sub_con,
(pd.qty-(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id)) AS qty_balance FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
LEFT JOIN wo_detail wd ON wd.pi_detail_id=pd.id
WHERE lpp.id = ‘1′ AND (SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) < pd.qty
GROUP BY pd.id

Wrong Result

The query returns AU-02 only instead of returning both AU-01A and AU-02.

I have no idea about this at the first time, but after look again at my query result, I notice that the condition:

..AND (SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) < pd.qty ..

is only valid if the result is not returning NULL value, that is why I got the result above.

Reading at MySQL Manual, Null value can be converted to Zero using COALESCE function. Quoting from MySQL Manual:

• COALESCE(value,…)Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

  mysql> SELECT COALESCE(NULL,1);
          -> 1
  mysql> SELECT COALESCE(NULL,NULL,NULL);
          -> NULL

  COALESCE() was added in MySQL 3.23.3.

Therefore I use this function in the query:

SELECT p.code AS product_code, pd.qty AS buyer_qty_order,
(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id) AS qty_ordered_to_sub_con,
(pd.qty-(SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id)) AS qty_balance FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
LEFT JOIN wo_detail wd ON wd.pi_detail_id=pd.id
WHERE lpp.id = ‘1′
AND COALESCE((SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id),0) < pd.qty
GROUP BY pd.id

Result Using COALESCE

Now the result is correct, but still has NULL value on the last two columns. Adding COALESCE at the query gives me the result I want.

SELECT p.code AS product_code, pd.qty AS buyer_qty_order,
COALESCE((SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id),0) AS qty_ordered_to_sub_con,
(pd.qty-COALESCE((SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id),0)) AS qty_balance FROM pi_detail pd
INNER JOIN product p ON p.id = pd.product_id
INNER JOIN lpp ON pd.pi_id = lpp.pi_id
LEFT JOIN wo_detail wd ON wd.pi_detail_id=pd.id
WHERE lpp.id = ‘1′
AND COALESCE((SELECT SUM(wd.qty) FROM wo_detail wd WHERE pd.id=wd.pi_detail_id),0) < pd.qty
GROUP BY pd.id

Correct Result

Finally I can continue my development..

Ups.. here is the screen-shoot for the application.