Select rows from table A where ID doesn't exists on table B or if exists, select only if is mor-CodePudding

I have two tables, one that stores the carts that the customers create by adding products in the front end of our ecommerce website, the other table (orders) is populated after the customer finishes checkout.

Some users have abandoned carts, so I want to check the carts table comparing it against the orders table by two conditions:

If user has a cart entry and no orders entries of his own, then select that cart
If user has a cart entry and some order entries, but user has no orders in the last three months and the cart is newer than three months, then select that cart

Table carts

cart_id   user_id      modified_on
5477        1125    2022-01-04 15:31:31
5476        2998    2022-01-04 14:34:31
5474        1305    2022-01-03 21:52:57
5473        986     2022-01-03 13:13:12
5471        3040    2022-01-03 01:32:28

Table orders

order_id    user_id    created_on
44           927      2018-11-23 00:26:43
46           932      2018-11-26 14:36:28
47           945      2018-11-26 15:35:34
48           948      2018-11-27 21:33:37
53           945      2018-12-02 18:20:55

So far I have come with this query nut I know it's wrong

SELECT DISTINCT
    `vmc`.`user_id`,
    `vmo`.`order_id`,
    `vmc`.`created_on` AS `Order Created On`,
    `vmc`.`modified_on` AS `Cart Last Modified`,
FROM `carts` `vmc`
LEFT JOIN `orders` `vmo`
   ON `vmc`.`user_id` = `vmo`.`user_id`
WHERE `vmo`.`order_id` IS NULL 
    OR (`vmo`.`created_on` <= NOW() - INTERVAL 3 MONTH AND `vmc`.`cart_id` <> NULL)
ORDER BY `vmc`.`modified_on` DESC

CodePudding user response：

The following query should give you what you want:

SELECT
    `carts`.`user_id`,
    `sq2`.`order_id`,
    `sq2`.`last_order_date` AS `Last Order Date`,
    `carts`.`cart_id`,
    `carts`.`modified_on` AS `Cart Last Modified`
FROM `carts`
LEFT JOIN (
    SELECT `user_id`, `order_id`, `sq1`.`last_order_date`
    FROM `orders` INNER JOIN  (
        SELECT `user_id`, MAX(`created_on`) as `last_order_date`
        FROM `orders`
        GROUP BY `order_id`
        ) `sq1` ON `orders`.`user_id` = `sq1`.`user_id`
) `sq2` ON `carts`.`user_id` = `sq2`.`user_id`
WHERE `sq2`.`order_id` IS NULL OR `sq2`.`last_order_date` <= NOW() - INTERVAL 3 MONTH
ORDER BY `carts`.`modified_on` DESC

The subquery with alias sq1 produces a relation of each unique user_id in the orders table with the maximum created_on date (aliased as last_order_date) for that user_id. The subquery with alias sq2 does an inner join of the previous query with the orders table so that we can also get the order_id column associated with that maximum created_on row for each user_id. Finally, we do a left join of the carts table with the sq2 table and apply our conditions.