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

Question

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  |

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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;