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
Order Table

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

Ordered Item

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
Balance Order

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
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

Result With Null

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

Result With Zero Value Instead of Null

Finally I can continue my development..

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

Screenshoot

Ditulis dalam Daily, Database, IT, PHP, Work. 14 Comments »

14 Tanggapan to “How To Convert Null Value to Zero On LEFT JOIN Query”

  1. tantos Says:

    woloh, ngeri ngunu dab kuerimu…..
    ajari sql aku….

  2. Arief Fajar Nursyamsu Says:

    Lah sampeyan kan sudah bikin SIKUDA..
    Querynya pasti lebih advance daripada punyaku..
    He he he he😀
    Silahkan bertanya.. kalau bisa saya jawab..
    klo ga bisa… RTFM aja…😀

  3. Joy Says:

    Hi,

    I use the method COALESCE for my application. But when i run the program, i have this error: Function COALESCE does not exists.
    I want to know what might be the problem.Its pretty important for me. I hope to receive a reply soon.

    Thanks in adavnce!

  4. Arief Fajar Nursyamsu Says:

    Did you use MySQL?
    I tested this with MySQL. It is a MySQL function, not in the programming language.

  5. sree Says:

    i want to know how to display NULL instead of all zeros in a table while using select query

  6. rholando Says:

    Hey man

    me salvaste😀

    GRACIAS
    Thanks

  7. ranjit singh Says:

    wao….!
    it works and my problem is solved dear….
    thank u.

  8. behrooz ghazvini Says:

    Tancks For We

  9. Rajen Says:

    Hi,
    Your post was quite helpful to me. Could you tell me how the same could be achieved with sqlite. That is, how null could be converted to 0 (or other values) with select statement ?

    Rajen.

  10. Rajen Says:

    Oops!! Sorry about that. Sqlite also has a function called coalesce().
    Rajen

  11. Arief Fajar Nursyamsu Says:

    @ Rajen
    Great to know that you solve your problem yourself.

    Thanks for visiting

  12. frenzal Says:

    this blog showed in google brilliantly when i was looking for a solution for the exact same problem, thanks for the tip!

  13. van Says:

    Hi,
    Thanks it works.!!!!!


Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: