WacksPoetic - 10 months ago 35

MySQL Question

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:

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/

Source (Stackoverflow)