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.

I fail to understand the logic. Can you explain what do you want to get as a result? — The Impaler, Jul 11 '20 at 13:11
Shouldn't there be a space after `Where Commodity =?`, or does it not matter? — Strawberry, Jul 11 '20 at 13:13
Dear Impaler I have given the desired result as the last table. — Renoj Joseph, Jul 11 '20 at 13:15
Dear Strawberry I have editted the query and added the space — Renoj Joseph, Jul 11 '20 at 13:18
It seems you want to fill the last order only. Is that what you want? — The Impaler, Jul 11 '20 at 13:18
Dear Impaler In Available stock the actual total Quantity is 55 but in result it is 130. Not possible to use Limit. — Renoj Joseph, Jul 11 '20 at 13:25
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 in result — Renoj Joseph, Jul 11 '20 at 13:42
If you don't have a PRIMARY KEY, then you don't really have a table. — Strawberry, Jul 11 '20 at 14:30
Dear Strawberry I have tried the query "Show Keys From table Where Key_name ='PRIMARY' "; In MySql WorkBench but it returned 0 row. And the table is working and giving results except for the error mentioned. — Renoj Joseph, Jul 11 '20 at 14:59

The Impaler · Accepted Answer · 2020-07-12T10:41:51.597

0

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);

edited Jul 12 '20 at 10:41

answered Jul 11 '20 at 13:24

The Impaler

45,731
9
39
76

Dear Impaler Max(date) will not solve the problem in general case because the problem here 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 in result – Renoj Joseph Jul 11 '20 at 13:54
Tried with the extra condition you have given. But getting an SQL Exception "No Database Selected". Also tried by changing the last p.purchprice to p.saleprice – Renoj Joseph Jul 11 '20 at 14:46
Your columns names and query are inconsistent. Please add your example to a https://www.db-fiddle.com/ – The Impaler Jul 11 '20 at 15:29
Ok I will do it – Renoj Joseph Jul 11 '20 at 15:32
here is the Link to db-fiddle https://www.db-fiddle.com/f/tkgUqhEJ4FXHHamMqLTNGN/0#&togetherjs=RVRg4MPqGa – Renoj Joseph Jul 11 '20 at 18:06
@RenojJoseph Updated the answer. The solution is also in the fiddle: https://www.db-fiddle.com/f/tkgUqhEJ4FXHHamMqLTNGN/1 – The Impaler Jul 11 '20 at 18:27
Dear Impaler thanks for your help. It works. It is taking a few seconds when only one commodity is taken. But it is taking infinite time to open up the table with all the commodities. My database is heavy with about 6000 commodities in purchase and 4000 commodities in available stock. I want the full table for comparison and to find the profit/loss . What to do? Any alternate way? – Renoj Joseph Jul 12 '20 at 08:56
@RenojJoseph See edit for performance. In order to improve performance there are many details that need to be analyzed, but I took a guess on a few things and I included the indexes that can be of help. Try adding them, and then you can try running the query again. – The Impaler Jul 12 '20 at 10:43
I have tried it and works well now it takes less than 2 minutes to open the full table. Thanks once again Impaler – Renoj Joseph Jul 12 '20 at 14:11

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

1 Answers1