mySQL inner join and replace value with latest value by date on all lines

Question

I have been looking around and trying to search but no luck on this.. I will continue to do so but would appreciate some help or being pointed to the right direction!

I have 2 datatables (similar to below)

sales_quotes

myIDX inStock   QDate   MFGname  MFGNumber  Vendor
+-----+-----+----------+--------+--------+----------+
|  1  | 20  | 3/4/2018 | Burton | snow1  | Christie |
+-----+-----+----------+--------+--------+----------+
|  2  | 100 | 3/8/2018 | Burton | snow1  | Christie |
+-----+-----+----------+--------+--------+----------+
|  3  | 35  | 3/8/2018 | Jones  | proto1 | Christie |
+-----+-----+----------+--------+--------+----------+
|  4  | 45  | 3/4/2018 | Jones  | proto1 | Christie |
+-----+-----+----------+--------+--------+----------+

sales_quotes_prices

 myIDX  myQTY myPrice
+-----+------+-------+
|  1  |  20  |  250  |
+-----+------+-------+
|  1  |  50  |  225  |
+-----+------+-------+
|  2  |  20  |  250  |
+-----+------+-------+
|  2  |  50  |  225  |
+-----+------+-------+
|  3  |  20  |  350  |
+-----+------+-------+
|  4  |  10  |  300  |
+-----+------+-------+
|  4  |  50  |  250  |
+-----+------+-------+

I have been able to use inner join to get sales_quotes broken out into multiple lines featuring every qty and price available for them.

SELECT q.myIDX
     , q.MFG
     , q.MFGN
     , q.Vendor
     , q.QDate
     , q.InStock
     , p.myQTY
     , p.myPrice
  FROM sales_quotes_prices p
  JOIN sales_quotes q
    ON p.myIDX = q.myIDX

result of join is below

+-------+---------+----------+---------+-----------+----------+-------+---------+
| myIDX | inStock | Qdate    | MFGname | MFGNumber | Vendor   | myQTY | myPrice |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 1     | 20      | 3/4/2018 | Burton  | snow1     | Christie | 20    | 250     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 1     | 20      | 3/4/2018 | Burton  | snow1     | Christie | 50    | 225     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 2     | 100     | 3/8/2018 | Burton  | snow1     | Christie | 20    | 250     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 2     | 100     | 3/8/2018 | Burton  | snow1     | Christie | 50    | 225     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 3     | 35      | 3/8/2018 | Jones   | proto1    | Christie | 20    | 350     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 4     | 45      | 3/4/2018 | Jones   | proto1    | Christie | 10    | 300     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 4     | 45      | 3/4/2018 | Jones   | proto1    | Christie | 50    | 250     |
+-------+---------+----------+---------+-----------+----------+-------+---------+

Goal: I am trying to replace the 'instock' decimal value from the latest 'QDate' on all matching quotes with the same [mfgname, mfgnumber, vendor]

But I am stuck on how to achieve this (below has the 'instock' appropriately allocated!)

+-------+---------+----------+---------+-----------+----------+-------+---------+
| myIDX | inStock | Qdate    | MFGname | MFGNumber | Vendor   | myQTY | myPrice |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 1     | 100     | 3/4/2018 | Burton  | snow1     | Christie | 20    | 250     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 1     | 100     | 3/4/2018 | Burton  | snow1     | Christie | 50    | 225     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 2     | 100     | 3/8/2018 | Burton  | snow1     | Christie | 20    | 250     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 2     | 100     | 3/8/2018 | Burton  | snow1     | Christie | 50    | 225     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 3     | 35      | 3/8/2018 | Jones   | proto1    | Christie | 20    | 350     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 4     | 35      | 3/4/2018 | Jones   | proto1    | Christie | 10    | 300     |
+-------+---------+----------+---------+-----------+----------+-------+---------+
| 4     | 35      | 3/4/2018 | Jones   | proto1    | Christie | 50    | 250     |
+-------+---------+----------+---------+-----------+----------+-------+---------+

Thanks for your suggestions!

@Strawberry thank you for the link! I hope that is better? My apologies if I missed anything else in the intros — J.Cam, Mar 08 '19 at 21:56
Just read, and act upon, the accepted answer at the link provided. — Strawberry, Mar 08 '19 at 22:11
@Strawberry I'm sorry but did I not already do that? I added plenty of easy sample data to show what I am looking to try and do? What part is confusing? I had already gone through the link, read it, and acted on it so if you can point me to the confusing part I will surely do my best to fix it. — J.Cam, Mar 08 '19 at 22:22

FanoFN · Answer 1 · 2019-03-11T23:59:01.743

0

This is not pretty but:

SELECT q.myIDX
     , B.InStock/*Instock data coming from subquery*/
     , q.QDate   
     , q.MFGname
     , q.MFGNumber
     , q.Vendor
     , p.myQTY
     , p.myPrice
  FROM sales_quotes_prices p
  JOIN sales_quotes q
    ON p.myIDX = q.myIDX
/*the subquery starts here*/        
LEFT JOIN
(SELECT a.myidx,a.instock,a.qdate,a.mfgname,a.mfgnumber,a.vendor FROM sales_quotes A INNER JOIN 
(SELECT *,MAX(qdate) AS latestdate FROM sales_quotes GROUP BY mfgname, mfgnumber, vendor) LATEST
ON a.qdate=latest.latestdate AND a.mfgname=latest.mfgname AND a.mfgnumber=latest.mfgnumber AND a.vendor=latest.vendor) B
ON q.mfgname=B.mfgname AND q.mfgnumber=B.mfgnumber AND q.vendor=b.vendor;

I'm using subquery to customize the "InStock" column only show the latest data. So the first subquery (which is within another subquery) is this:

SELECT *,MAX(qdate) AS latestdate FROM sales_quotes GROUP BY mfgname, mfgnumber, vendor .

The MAX(qdate) will return the latest date group by your condition of mfgname, mfgnumber, vendor which I define as "latestdate". Then I write another query to INNER JOIN sales_qoutes table with the first subquery on qdate=latestdate, which you can see here:

SELECT a.myidx,a.instock,a.qdate,a.mfgname,a.mfgnumber,a.vendor FROM sales_quotes A INNER JOIN (SELECT *,MAX(qdate) AS latestdate FROM sales_quotes GROUP BY mfgname, mfgnumber, vendor) LATEST ON a.qdate=latest.latestdate AND a.mfgname=latest.mfgname AND a.mfgnumber=latest.mfgnumber AND a.vendor=latest.vendor

Of course, I need to state that mfgname, mfgnumber and vendor must also match. This will force the second subquery to return only latest data matched with MAX(qdate) from the first subquery. And the last one is just to edit your original syntax by changing the q.instock to b.instock (or whatever derive table name you define with for the subquery) and LEFT JOIN it with the subqueries.

edited Mar 11 '19 at 23:59

answered Mar 09 '19 at 03:27

FanoFN

6,815
2
13
33

That works! I really appreciate it! (I had to change "AND B.vendor=b.vendor;" to "AND q.Vendor=B.Vendor") So it takes about 10 seconds to run on our tables which are Sales_quotes - 3636 rows, Sales_quotes_prices - 11306 rows. Is there any suggestion for efficiency on this? I'm thinking maybe I'll wait to run the in stock portion until I narrow down the 10 to 100 items we are actually quoting and just run it on those – J.Cam Mar 11 '19 at 15:24
Might even be more efficient to move the 'in stock' portion into the code area after retrieving the table from SQL and sorting it? I know at some point once we implement the quoting fully, we will get to hundreds of thousands that we are looking at within a 6month date. – J.Cam Mar 11 '19 at 15:36
using the ORDER BY MFGnumber ASC, MFGname ASC, Vendor ASC, QDate DESC and then running a function to go down the table replacing the latest inStock decimal only takes 20ms on average to run on the 11.5k rows. The order by select function takes 200ms on avg (I ran 5 times). So 220ms to run it this way. Using the above sub-queries it was taking about 10 seconds to run as I mentioned. Is there a more time efficient method or do you think it might be best to stick with the outside function to achieve this? Thanks so much for your help! – J.Cam Mar 11 '19 at 16:31
I'll try with a bigger data @J.Cam and will update soon. – FanoFN Mar 11 '19 at 23:50
My mistake on that "AND B.vendor=b.vendor;", I have edited that part. – FanoFN Mar 11 '19 at 23:59
@J.Cam, I have tried query again and I get close to 2secs at most on a larger (around 16k) data. I can only suggest that you may have to do some indexing and prevent using asterisk in `SELECT` for the subquery (which actually what I did). So, instead of typing `SELECT * ,MAX(qdate) AS latestdate FROM sales_quotes GROUP BY mfgname, mfgnumber, vendor`, I should only select the columns that I want to use for joining and the MAX(qdate). `SELECT mfgname, mfgnumber, vendor,MAX(qdate) AS latestdate FROM sales_quotes GROUP BY mfgname, mfgnumber, vendor`. – FanoFN Mar 12 '19 at 00:35
As for indexing, I suggest you take a look at Rick James's "MySQL INDEX Cookbook" (which I found about only yesterday and still learning about it) at http://mysql.rjweb.org/slides/cook.pdf . You could also look at his "Rules of Thumb for MySQL" http://mysql.rjweb.org/doc.php/ricksrots . I found it very useful and most importantly **short and easy to understand**. I'm sorry I couldn't help more and I hope you can figure out the best most efficient way to get your results. ;) – FanoFN Mar 12 '19 at 00:39
You've been most helpful! I really appreciate the info. I will be looking into this indexing soon. That is a good point I am probably drawing more data than I need. At some point I know it'll be in hundreds of thousands of lines so I'll have to get more efficient soon, but it's in a really good spot now! Thanks for the help – J.Cam Mar 12 '19 at 22:07

score 0 · Answer 2 · answered Mar 12 '19 at 00:09

For a more elegant implementation, you can check CTEs and windowing functions: http://www.mysqltutorial.org/mysql-window-functions/ And here's a cross-db example: https://stackoverflow.com/a/3800572/1710180 The problem is referred to as "selecting top row by group"

mySQL inner join and replace value with latest value by date on all lines

sales_quotes

sales_quotes_prices

2 Answers2