'Mysql join multiple tables with sum of fields
I need to get data from three tables.I have four tables "orders", "ordersdetail","reserved" and "deliver". In "orders" table are pick orders for picking items from a warehouse, "ordersdetail" table contain the list of items to be picked, in "reserved" table are picked items and in "deliver" is the list of delivered items.These are the tables :
ordersdetail:
|ido|barcode | quantity| order_id|
==================================
| 1 | 123 | 2 | 1 |
| 2 | 456 | 1 | 1 |
| 3 | 789 | 3 | 1 |
| 4 | 456 | 1 | 2 |
reserved:
|idr|barcode | quantity| order_id|
==================================
| 1 | 123 | 1 | 1 |
| 2 | 123 | 1 | 1 |
| 3 | 456 | 1 | 1 |
| 4 | 789 | 1 | 1 |
| 5 | 456 | 1 | 2 |
deliver:
|idr|barcode | quantity| order_id|
==================================
| 1 | 123 | 1 | 1 |
| 2 | 456 | 1 | 1 |
| 3 | 456 | 1 | 2 |
I need an sql to get data for order_id=1 to sum quantity from tables grouped by barcode:
|order_id|barcode|order_qt|reserved_qt|delivered_qt|
====================================================
| 1 | 123 | 2 | 2 | 1 |
| 1 | 456 | 1 | 1 | 1 |
| 1 | 789 | 2 | 1 | 0 |
I tried with first two table but i cant get it:
SELECT m.barcode,m.quantity,m.order_id,(SELECT SUM(reserved.quantity) from reserved where reserved.barcode=m.barcode and reserved.order_id=m.order_id) FROM orderdetail AS m
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
