AJAY KUMAR AJAY KUMAR - 3 years ago 129
MySQL Question

SQL query to join 4 Tables and get sum of 2 columns from 2 different tables

Table 1: Purchase Report




|id| VoucherNO| VoucherDate|
+--+----------+------------+
|1 | 0001 | 28/9/2017 |
|2 | 0010 | 27/9/2017 |


Table 2: Detail Purchase Report




|id | ITEMID | QTYpurchased | voucher
+---+--------+--------------+--------
|1 | 101 | 12 | 0001
|2 | 120 | 25 | 0001
|3 | 121 | 21 | 0014


Table 3: Sale Report




|id| VoucherNO| VoucherDate|
+--+----------+------------+
|1 | 0025 | 25/9/2017 |
|2 | 0058 | 23/9/2017 |


Table 4: Detail Sale




|id | ITEMID | QTYpurchased | voucher
+---+--------+--------------+--------
|1 | 101 | 8 | 0025
|2 | 120 | 5 | 0025
|3 | 121 | 10 | 0058


Objective of the query is to join all 4 Tables to return 2 Columns
ie.,


  • Column 1 : Item Name

  • 2 : Difference Purchased qty and sold qty having same item id
    in between given dates gives the qty of a item at that particular date.



Query I tried is as follows

SELECT
A.ITEMN,
A.ITEMNAME,
SUM(CAST(C.QUANTITY AS numeric(18,2))) - SUM(CAST(A.QTY AS numeric(18,2))) as QTY
FROM DETAILSALE A, SALESREPORT B, DETAILPURCHASES C, PURCHASEREPORT D
WHERE A.BARCODE = B.VOUCHERNO
AND C.BARCODE = D.VOUCHERNO
AND D.VOUCHERDATE=B.VOUCHERDATE
AND D.VOUCHERDATE BETWEEN '" & DATE1.Text & "' AND '" & DATE2.Text & "'
GROUP BY A.ITEMN,A.ITEMNAME
ORDER BY A.ITEMN ASC


Required Output:




| ITEMID | QTY |
+--------+-----+
| 101 | 2 |
| 120 | 10 |
| 121 | 10 |

Answer Source

Aggregate first and only join then. I am missing a product table from your description and names in your table descriptions and query don't match, so you may have to adjust names in my query:

select
  product.id,
  product.name,
  purchases.total,
  sales.total
from product
left join
(
  select itemid, sum(quantity) as total
  from detail_purchase
  where voucher in (select voucher from purchase where date between date '2017-09-01' 
                                                                and date '2017-09-05')
  group by itemid
) purchases on purchases.itemid = product.itemid
left join
(
  select itemid, sum(quantity) as total
  from detail_sale
  where voucher in (select voucher from sale where date between date '2017-09-01'
                                                            and date '2017-09-05')
  group by itemid
) sales on sales.itemid = product.itemid;

Change outer joins to inner joins, if you only want products that where bought and/or sold.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download