Mr Tom Mr Tom - 2 months ago 5
MySQL Question

mysql two table join sum not equal result

I have two tables

purchase_orders_details
and
stock_orders_details
I show in below of my paragraph

purchase_orders_details

--------------------
item_id | item_qty |
--------------------
| 1 | 15 |
| 2 | 10 |
| 1 | 20 |
--------------------

stock_orders_details
------------------------
item_id | item_rcv_qty |
------------------------
| 1 | 10 |
| 2 | 5 |
| 1 | 15 |
------------------------


The query example is:

SELECT * FROM purchase_orders_details p
left join stock_details d on p.item_qty=d.item_rcv_qty
WHERE d.item_rcv_qty IS NULL


I want to result like this, and only result show who not matched with
item_qty not equal item_rcv_qty
because when both quantity is equal then order will be completed.

-------------------------------------------------
Item Id | Item Quantity | Received Quantity |
-------------------------------------------------
| 1 | 35 | 25 |
| 2 | 10 | 5 |
-------------------------------------------------

Answer

Your expected output looks as though you are just aggregating the item quantities and item received quantities for each item_id. If so, then one way to approach this is to join together two subqueries which compute the aggregations you want.

SELECT t1.item_id,
       t1.item_qty AS item_quantity,
       COALESCE(t2.item_rcv_qty, 0) AS receieved_quantity
FROM
(
    SELECT item_id, SUM(item_qty) AS item_qty
    FROM purchase_orders_details
    GROUP BY item_id
) t1
LEFT JOIN
(
    SELECT item_id, SUM(item_rcv_qty) AS item_rcv_qty
    FROM stock_orders_details
    GROUP BY item_id
) t2
    ON t1.item_id = t2.item_id
Comments