MySQL Forums
Forum List  »  Newbie

Help with structuring a nested query
Posted by: Ken Beauchamp
Date: August 08, 2022 04:47AM

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

Options: ReplyQuote


Subject
Written By
Posted
Help with structuring a nested query
August 08, 2022 04:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.