How to SELECT customers with orders before specific date-CodePudding

I have two table.

Table 'customers': customer_id, date_register, name

Table 'orders': order_id, customer_id, order_date

Now I want the customers who have orders before specific date and have NOT after that date.

I am using this query:

SELECT customer_id
  FROM orders
 WHERE EXISTS (SELECT order_id
                 FROM orders
                WHERE order_date <= '2020-05-12 23:59:59')
   AND NOT EXISTS (SELECT order_id
                     FROM orders
                    WHERE order_date > '2020-05-12 23:59:59')

But I get empty result.

What SQL query should I use?

CodePudding user response：

You can use aggregation and set the condition in the HAVING clause:

SELECT customer_id 
FROM orders
GROUP BY customer_id 
HAVING MAX(order_date) < '2020-05-13';

CodePudding user response：

You could try selecting the customer based on the two range using main quary and a subquery in left join then checking for not macthing id

select distinct customer_id 
FROM orders 
LEFT JOIN (
    select distinct customer_id 
    FROM orders 
    WHERE order_date > '2020-05-12 23:59:59'
) t on t.customer_id = orders.customer_id
    and orders.order_date <= '2020-05-12 23:59:59'
WHERE t.id is null