Help with structuring a nested query

[Ken Beauchamp]

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