Want to prevent MySQL Inner Join two tables giving Unwanted Rows In Result Java Swing

Question

Dear friends I am developing Java Swing Project and I have 2 Mysql table as follows.

Table Purchase

+------------+-----------+------------+-----------+-----+
|    Date    | Commodity | PurchPrice | SalePrice | Qty |
+------------+-----------+------------+-----------+-----+
| 2020-06-10 | A         |        123 |     150.0 |  15 |
| 2020-06-12 | A         |        125 |     150.0 |  25 |
| 2020-06-14 | A         |        120 |     150.0 |  30 |
| 2020-06-16 | A         |        124 |     150.0 |  35 |
| 2020-06-18 | A         |        126 |     160.0 |  40 |
+------------+-----------+------------+-----------+-----+

Table AvailStock

+-----------+-----------+-----+
| Commodity | SalePrice | Qty |
+-----------+-----------+-----+
| A         |     150.0 |  25 |
| A         |     160.0 |  30 |
+-----------+-----------+-----+

This is my Sql query

String query = "SELECT Distinct Date,Purchase.Commodity,AvailStock.Commodity, "
        PurchPrice,Purchase.SalePrice,AvailStock.SalePrice,AvailStock.Qty "
        + "From AvailStock "
        + "Inner Join Purchase "    
        + "On Purchase.SelePrice = AvailStock.SalePrice "
        + "And Purchase.Commodity = AvailStock.Commodity "
        + "Where Commodity =? "
        + "And AvailStock.Qty!=0 ";

It gives the following result.

+------------+-----------+------------+-----------+-----+
|    Date    | Commodity | PurchPrice | SalePrice | Qty |
+------------+-----------+------------+-----------+-----+
| 2020-06-10 | A         |        123 |       150 |  25 |
| 2020-06-12 | A         |        125 |       150 |  25 |
| 2020-06-14 | A         |        120 |       150 |  25 |
| 2020-06-16 | A         |        124 |       150 |  25 |
| 2020-06-18 | A         |        126 |       160 |  30 |
+------------+-----------+------------+-----------+-----+

It is giving an exaggerated output for Qty

And I want the following result

+------------+-----------+------------+-----------+-----+
|    Date    | Commodity | PurchPrice | SalePrice | Qty |
+------------+-----------+------------+-----------+-----+
| 2020-06-16 | A         |        124 |       150 |  25 |
| 2020-06-18 | A         |        126 |       160 |  30 |
+------------+-----------+------------+-----------+-----+

Tried Left join and Right Join istead of Inner Join and all gives the same result!!!.

Here the problem is that Commodity A is having two sale price. viz 150 and 160. Out of these 160 have only one puchase price. So the result in case of Commodity A with sale price 160 is correct.

But in case of Commodity A with Sale price 150 have 4 different purchase price So in result 4 rows are coming corresponding to each purchase price resulting in error with total avalable Quantity.

Somebody please help me to get the desired result.

Note:- Actually this error is happening only to those Commodities which are having different purchase price but same sale price as illustrated in the above table.

Answer 1

If you want to fill the last order only, then you'll need to filter out all "older" rows. You can do this by adding the following extra condition to the WHERE clause:

SELECT Date,AvailStock.Commodity,
        AvailStock.SalePrice,PurchPrice,AvailStock.Qty
From Purchase
Inner Join AvailStock   
        On Purchase.SalePrice = AvailStock.SalePrice 
        And Purchase.Commodity = AvailStock.Commodity 
Where AvailStock.Commodity ='A'
        And AvailStock.Qty!=0
        and (AvailStock.Commodity, AvailStock.SalePrice, Purchase.Date) in (
  select p.Commodity, p.SalePrice, max(date)
  from Purchase p
  join AvailStock a on a.Commodity = p.Commodity 
                   and a.SalePrice = p.SalePrice
  group by p.Commodity, p.SalePrice
)           

EDIT for performance

The following indexes can theoretically improve the performance of this query:

create index ix1 on AvailStock (Commodity, Qty);

create index ix2 on Purchase(Commodity, SalePrice, Date);

create index ix3 on AvailStock (Commodity, SalePrice);