64QzWSMq

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  ('1', '5','12344242', 'XXXX','2020-11-11 13:11:05'),
30  ('2', '1','12344242', 'XXXX','2020-11-06 13:11:00'),
31  ('2', '7','11112', 'YYY','2020-11-06 13:11:00'),
32  ('2', '4','12344242', 'XXXX','2020-11-25 13:11:05'),
33  ('3', '6','12344242', 'XXXX','2020-11-09 13:11:05');
34
35/* FIRST ONE: */
36 
37SELECT carts.*
38FROM `user_cart` carts
39INNER JOIN ( SELECT cart_id, barcode, added_at
40FROM  `cart_items`
41WHERE barcode = '12344242' AND added_at >= subdate(now(),2)) items
42ON carts.cart_id = items.cart_id
43GROUP BY cart_id
44
45/* SECOND ONE: */
46
47SELECT * 
48FROM `cart_items` items
49RIGHT JOIN(
50  SELECT cart_id
51  FROM (SELECT * FROM cart_items 
52      WHERE barcode = '12344242' AND added_at >= subdate(now(), 8)
53      ORDER BY added_at DESC) sorted
54GROUP BY `cart_id`
55ORDER BY `added_at` DESC
56LIMIT 10) top
57ON top.cart_id = items.cart_id
58