AJAY KUMAR - 3 years ago 139
MySQL 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  |
```

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