WacksPoetic WacksPoetic - 3 months ago 11
MySQL Question

How can I compare the sum of one field to another single field?

My data set is like so:


  • Total_Order_Table

  • Order_no (unique)

  • Shipped_quantity

  • Order_Detail_Table

  • Order_number (not unique)

  • Quantity_per_bundle



I need to take the sum of Quantity_per_bundle for each order_number from Order_Detail_Table and compare it to the Shipped_quantity.

My idea is an outer join so that my data will look like so:

enter image description here

I need to be able to see quantity discrepancies and if the order number exists in both tables.

Thanks in advance!

Answer

Normaly with a FULL OUTER JOIN in sql:

SELECT to.Order_no AS Order_no_Total_Order_Table, od.Order_number AS Order_No_Ordr_detail_Table, SUM(od.Order_number) AS sum_Quanitty_Per_Bundle, od.Order_number 
FROM Total_Order_Table AS to
FULL OUTER JOIN Order_Detail_Table AS od ON  to.Order_no = od.Order_number
GROUP BY to.Order_no

But FULL OUTER JOIN don't exist in mysql. But you can simulate it : http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/