-
Aug 8th, 2022, 05:49 AM
#1
Thread Starter
Lively Member
Mysql Nested sub query
Dear All
I am trying to run a query that returns inventory items and the last purchase date, price and order number used to make the purchase
I am guessing that a sub query would do this but am struggling to get this to work.
Here are my tables:
CREATE TABLE `inventory` (
`item_code` char(5) NOT NULL,
`item_description` varchar(20) default NULL,
`item_unit` varchar(5) default 'Ea',
PRIMARY KEY (`item_code`),
UNIQUE KEY `item_code` (`item_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `inventory_purchases` (
`purchase_id` int(11) NOT NULL auto_increment,
`item_code` char(5) default NULL,
`order_number` varchar(5) default NULL,
`order_date` date default NULL,
`price` decimal(8,2) default NULL,
PRIMARY KEY (`purchase_id`),
UNIQUE KEY `purchase_id` (`purchase_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
INSERT INTO `inventory` (`item_code`, `item_description`, `item_unit`) VALUES
('ABC01','Item Number One','Ea'),
('ABC02','Item Number Two','Ea'),
('ABC03','Item Number Three','Ea');
COMMIT;
INSERT INTO `inventory_purchases` (`purchase_id`, `item_code`, `order_number`, `order_date`, `price`) VALUES
(1,'ABC01','1234','2022-07-01',10.54),
(2,'ABC01','1235','2022-07-06',10.84),
(3,'ABC03','1236','2022-07-07',35.42),
(4,'ABC02','1237','2022-07-07',22.36),
(5,'ABC01','1238','2022-07-09',10.33),
(6,'ABC02','1239','2022-07-11',21.58);
COMMIT;
I need a query that returns the following:
item_code |item_description| item_unit|order_number|order_date|price
ABC01|Item Number One |Ea |1238 |2022-07-09| 10.33
ABC02|Item Number Two |Ea |1239 |2022-07-11| 21.58
ABC03|Item Number Three |Ea |1236 |2022-07-07| 35.42
any assistance would be greatly appreciated
Regards
Ken
-
Aug 8th, 2022, 06:15 AM
#2
Re: Mysql Nested sub query
Tested in SQLite (No MySQL at Hand).
ROW_NUMBER might have a different Syntax on MySQL, but researching it couldn't find any difference
sql Code:
SELECT i.item_code, i.item_description, i.item_unit, ip.order_number, ip.order_date, ip.price FROM inventory As i LEFT JOIN (SELECT item_code, order_number, order_date, price, ROW_NUMBER() OVER(PARTITION BY item_code ORDER BY order_number DESC, order_date DESC) As RN FROM inventory_purchases) As ip ON i.item_code=ip.item_code and ip.rn=1 ORDER BY i.item_code
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Aug 8th, 2022, 06:17 AM
#3
Re: Mysql Nested sub query
Returns:
ABC01 |
Item Number One |
Ea |
1238 |
2022-07-09 |
10.33 |
ABC02 |
Item Number Two |
Ea |
1239 |
2022-07-11 |
21.58 |
ABC03 |
Item Number Three |
Ea |
1236 |
2022-07-07 |
35.42 |
Notes: If order_number is unique (which it should be) you can leave out the second order by-argument of order_date within the ROW_NUMBER.
OTOH, if you need the last order, first going by date, then by order_number, you'd need both (and swap the arguments: Date first, number second)
since you can have more than one order on the same day, but one order can only have one date
EDIT: Oh, and item_code in inventory_purchases should be Foreign Key to item_code in inventory, including ON UPDATE CASCADE,
except it's intentional
EDIT2: Important in this case: the ".... ip.RN=1" must stay in the ON-Clause.
If you move it to the WHERE-Clause of the "outer" SELECT you won't see items from "inventory" which don't have any purchase so far
Last edited by Zvoni; Aug 8th, 2022 at 06:27 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Aug 8th, 2022, 07:03 AM
#4
Thread Starter
Lively Member
Re: Mysql Nested sub query
Hi Zvoni
Thank you so much, when i run the query i get the following error:
'VIEW'S SELECT CONTAINS A SUB QUERY IN THE FROM CLAUSE'
Any Ideas?
-
Aug 8th, 2022, 07:04 AM
#5
Thread Starter
Lively Member
Re: Mysql Nested sub query
-
Aug 8th, 2022, 07:21 AM
#6
Re: Mysql Nested sub query
Originally Posted by KenBZim2
I am using Mysql 5.0
OUCH! Why such an old version?
Originally Posted by KenBZim2
Hi Zvoni
Thank you so much, when i run the query i get the following error:
'VIEW'S SELECT CONTAINS A SUB QUERY IN THE FROM CLAUSE'
Any Ideas?
https://dev.mysql.com/doc/refman/5.6...eate-view.html
A view definition is subject to the following restrictions:
- The SELECT statement cannot contain a subquery in the FROM clause.
If i understand it correctly, with MySQL5.7 and newer you don't have that restriction, MySQL5.6 and older have that restriction
Workaround: Create a separate View for the Inner SELECT (The one inside the LEFT JOIN).
Note: You'll probably pay performance penalties for the (multiple) views
First view ("helper-view"):
sql Code:
CREATE VIEW "vw_ip" AS SELECT item_code, order_number, order_date, price, ROW_NUMBER() OVER(PARTITION BY item_code ORDER BY order_number DESC, order_date DESC) As RN FROM inventory_purchases
Second View (which would be your "main" view)
sql Code:
CREATE VIEW "vw_inventory_purchases" AS SELECT i.item_code, i.item_description, i.item_unit, ip.order_number, ip.order_date, ip.price FROM inventory As i LEFT JOIN vw_ip As ip ON i.item_code=ip.item_code and ip.rn=1 ORDER BY i.item_code
See also: https://stackoverflow.com/questions/...he-from-clause
Last edited by Zvoni; Aug 8th, 2022 at 07:27 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Aug 8th, 2022, 08:11 AM
#7
Re: Mysql Nested sub query
Try this query without a window function (the ROW_NUMBER bit)
https://www.db-fiddle.com/f/tZJbvdrRUNUrx8g18Ui9Ev/1
It's less efficient than using window functions but a lot more compatible and should work both in MySQL in MS Access too for instance.
cheers,
</wqw>
p.s. Now I see that @Zvoni uses ORDER BY order_number DESC, order_date DESC while the db-fiddle above uses ORDER BY `order_date` DESC, `order_number` DESC which might be more correct IMO.
-
Aug 8th, 2022, 08:41 AM
#8
Re: Mysql Nested sub query
Originally Posted by wqweto
Try this query without a window function (the ROW_NUMBER bit)
https://www.db-fiddle.com/f/tZJbvdrRUNUrx8g18Ui9Ev/1
It's less efficient than using window functions but a lot more compatible and should work both in MySQL in MS Access too for instance.
cheers,
</wqw>
p.s. Now I see that @Zvoni uses ORDER BY order_number DESC, order_date DESC while the db-fiddle above uses ORDER BY `order_date` DESC, `order_number` DESC which might be more correct IMO.
Argg...and additionally i missed that ROW_NUMBER is only available in MySQL8 and later, not earlier.
Damn!
wqweto, OP's Problem is, that he wants to create a View for that Statement, and MySQL5.6 and earlier doesn't permit Sub-Selects in Joins.
OTOH, he could "export" each nested SELECT into its own View
btw: I addressed the sorting criteria, too :-)
but i noticed that order_number seems to be unique (kind of AUTO-INCREMENT)
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Aug 8th, 2022, 09:01 AM
#9
Re: Mysql Nested sub query
Got it without ROW_NUMBER
sql Code:
SET @rn = 0; SET @ic =''; SELECT i.item_code, i.item_description, i.item_unit, ip.order_number, ip.order_date, ip.price FROM inventory As i LEFT JOIN (SELECT order_number, order_date, price, (@rn:=CASE WHEN @ic=item_code THEN @rn+1 ELSE 1 END) As RN, @ic:=item_code As item_code FROM inventory_purchases ORDER BY item_code ASC, order_date DESC, order_number DESC) As ip ON i.item_code=ip.item_code and ip.rn=1 ORDER BY i.item_code
No idea if you can use session variables in a view
(EDIT: Probably not.
A view definition is subject to the following restrictions:
- The SELECT statement cannot contain a subquery in the FROM clause.
- The SELECT statement cannot refer to system variables or user-defined variables.
And reading the MySQL-Docs you cannot use user-variables in a View. Period!
So either the OP uses wqweto's solution incl. "exporting" the nested SELECT's to be their own view, or upgrades to MySQL8.
...or doesn't use a view at all.....
Inspiration from here: https://www.mysqltutorial.org/mysql-row_number/
sqlfiddle: http://sqlfiddle.com/#!9/88acc0/1
Last edited by Zvoni; Aug 8th, 2022 at 09:11 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Aug 9th, 2022, 12:15 AM
#10
Thread Starter
Lively Member
Re: Mysql Nested sub query
Thanks guys
I cannot get any of the above to work. Guess it is time to upgrade to Mysql 8
-
Aug 9th, 2022, 01:08 AM
#11
Re: Mysql Nested sub query
Originally Posted by KenBZim2
Thanks guys
I cannot get any of the above to work. Guess it is time to upgrade to Mysql 8
Probably your best bet
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Aug 11th, 2022, 09:36 PM
#12
Hyperactive Member
Re: Mysql Nested sub query
I use MySQL 5.xx and the following should work if you still need a solution.
Code:
SELECT `i`.`item_code`
, `i`.`item_description`
, `i`.`item_unit`
, `ip`.`order_number`
, `ip`.`order_date`
, `ip`.`price`
FROM `inventory` i
INNER JOIN
`inventory_purchases` ip
ON (`i`.`item_code` = `ip`.`item_code`)
WHERE `ip`.`order_date` = (SELECT MAX(`order_date`) FROM `inventory_purchases` p2 WHERE `ip`.`item_code` = `p2`.`item_code`)
ORDER BY `i`.`item_code`;
-
Aug 12th, 2022, 01:57 AM
#13
Re: Mysql Nested sub query
Same item_code can be ordered twice on the same date and in practice this happens all the time when operators remember they need a little bit extra of particular item_code later the same day they ordered it already.
-
Aug 12th, 2022, 03:19 AM
#14
Re: Mysql Nested sub query
.... and don't use INNER JOIN ... items from Inventory without inventory_purchases are discarded with INNER JOIN.
Use LEFT JOIN
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Aug 12th, 2022, 07:37 AM
#15
Hyperactive Member
Re: Mysql Nested sub query
wqweto, you are correct but the OP did not include that in their requirements, nor did they indicate that in their own sample SQL that they tried. Had those indicators been there, the SQL statement I used may have been different. At this point we don't know how to handle it because we don't know how the OP would want it handled.
Had the OP needed the very last order from the last purchase date for each item, then I might have set the WHERE subquery to order by `purchase_id` DESC and then set a LIMIT 1 option as a starting point to test. This may have required an additional part to the WHERE clause. However, do you want the last order entered (by purchase_id) or the last order entered (by order_no) or the first order of the day or the largest order by quantity, or dollars? The above should give the OP a starting point to modify.
-
Aug 12th, 2022, 07:54 AM
#16
Hyperactive Member
Re: Mysql Nested sub query
Originally Posted by Zvoni
.... and don't use INNER JOIN ... items from Inventory without inventory_purchases are discarded with INNER JOIN.
Use LEFT JOIN
Zvoni, i interpreted the OP's question with the SQL statement I created. Yes, you are correct, a LEFT JOIN would be a better option if the OP wanted all items, and the last purchase for each if applicable. But I didn't see that in Post #1. The OP will have to be careful about using any WHERE that uses fields from the second table in the join of the outer query because that will force the join back to an INNER JOIN without some special handing of NULL values.
-
Aug 12th, 2022, 08:18 AM
#17
Re: Mysql Nested sub query
Originally Posted by rasinc;[URL="tel:5577077"
5577077[/URL]]Zvoni, i interpreted the OP's question with the SQL statement I created. Yes, you are correct, a LEFT JOIN would be a better option if the OP wanted all items, and the last purchase for each if applicable. But I didn't see that in Post #1. The OP will have to be careful about using any WHERE that uses fields from the second table in the join of the outer query because that will force the join back to an INNER JOIN without some special handing of NULL values.
Rasinc,
look closely at my left join, especially the rownumber RN. I filter in the ON-clause
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Aug 12th, 2022, 08:59 PM
#18
Hyperactive Member
Re: Mysql Nested sub query
Zvoni, I do admit that when I saw the use of Partition, I did not dissect your query very closely. I knew Partition was not useable in version 5.7 and i have not read much about version 8 yet. So because this thread had already determined the versions I did not look that closely, sorry.
I have used ON to filter but I don't think I have ever checked to see if it avoids forcing an INNER JOIN when selecting from the right table. Thank you for bringing that to my attention.
-
Aug 15th, 2022, 05:59 AM
#19
Re: Mysql Nested sub query
Originally Posted by rasinc
Zvoni, I do admit that when I saw the use of Partition, I did not dissect your query very closely. I knew Partition was not useable in version 5.7 and i have not read much about version 8 yet. So because this thread had already determined the versions I did not look that closely, sorry.
I have used ON to filter but I don't think I have ever checked to see if it avoids forcing an INNER JOIN when selecting from the right table. Thank you for bringing that to my attention.
See also this discussion: https://www.vbforums.com/showthread....n-WHERE-Clause
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|