fJWcMhnj

1-- borrowed from https://stackoverflow.com/q/7745609/808921
2
3CREATE TABLE IF NOT EXISTS `user_cart` (
4  `cart_id` int(3) unsigned NOT NULL,
5  `user_id` int(3) unsigned NOT NULL,
6  `created_at` Date  NOT NULL,
7  `closed_at` Date,
8  PRIMARY KEY (`cart_id`)
9) DEFAULT CHARSET=utf8;
10
11
12INSERT INTO `user_cart` (`cart_id`, `user_id`, `created_at`) VALUES
13  ('1', '123', '2020-10-01 13:10:05'),
14  ('2', '124', '2020-10-02 14:10:05');
15 
16 
17CREATE TABLE IF NOT EXISTS `cart_items` (
18  `cart_id` int(3) unsigned NOT NULL,
19  `item_id` int(3) unsigned NOT NULL,
20  `barcode` int(6) unsigned NOT NULL,
21  `item_name` varchar(100) NOT NULL,
22  `added_at` Date  NOT NULL,
23  PRIMARY KEY (`item_id`)
24) DEFAULT CHARSET=utf8;
25
26
27INSERT INTO `cart_items` (`cart_id`, `item_id`, `barcode`, `item_name`,`added_at`) VALUES
28  ('1', '2','12344242', 'XXXX','2020-11-08 13:11:05'),
29  ('2', '3','12344242', 'XXXX','2020-11-08 13:11:05'),
30  ('1', '4','542342331', 'YYYY','2020-10-01 13:12:10'),
31  ('2', '1','12344242', 'XXXX','2020-11-08 13:11:00');
32
33
34
35SELECT carts.*
36FROM `user_cart` carts
37INNER JOIN ( SELECT cart_id, barcode, added_at
38FROM  `cart_items`
39WHERE barcode = '12344242' AND added_at >= subdate(now(),2)) items
40ON carts.cart_id = items.cart_id
41GROUP BY cart_id