-2

Hi I have a Join Statement Query

Table 1 = Items Table 2 = Purchase Orders

Some items are not in the purchase order table so leaving a column blank when fetch a data from purchase orders to join in the Items Table.

Objective is to make the null column 0 when the item id is not in the purchase order table

Table 1

 |Item ID | Name | Quantity |
    ---------------------------
    |   1    | IT1  |    5    |
    |   2    | IT2  |    10    |
    |   3    | IT3  |    15    |
    ----------------------------

Table 2

| PO ID  | PO Name | Ordered  | Item ID |
----------------------------------------
|   1    |   PO1   |    10    |     1   |
|   2    |   PO2   |    20    |     2   |
|   3    |   PO3   |    15    |         |
----------------------------------------

JOIN TABLE

Since PO3 Item ID has no corresponding Id to the item when join table should make it 0

 | ID  | PO Name  | PO Ordered | Item Quantity |
 -----------------------------------------------
 |  1  |   PO1    |     10     |      5        |
 |  2  |   PO2    |     20     |      10       |
 |  3  |   PO3    |     15     |      0        |
 -----------------------------------------------
Ross
  • 9
  • 2
  • Does this answer your question? [How to use Coalesce in MySQL](https://stackoverflow.com/questions/17104329/how-to-use-coalesce-in-mysql) – AD7six Jul 26 '22 at 08:31
  • Can you show your code? Have you tried `isnull` https://www.tutorialspoint.com/mysql/mysql-null-values.htm or https://www.c-sharpcorner.com/UploadFile/65fc13/how-to-handle-the-null-values-in-mysql-server/ or tried anything? – Nathan_Sav Jul 26 '22 at 08:31

1 Answers1

2

Left join the second table to the first table:

SELECT t2.`PO ID` AS ID, t2.`PO Name`, t2.Ordered AS `PO Ordered`,
       COALESCE(t1.Quantity, 0) AS `Item Quantity`
FROM Table2 t2
LEFT JOIN Table1 t1
    ON t1.`Item ID` = t2.`Item ID`
ORDER BY t2.`PO ID`;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360