Search code examples
mysqldategroup-bymaxhaving

How to SELECT customers with orders before specific date

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?

Solution

  • 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';