CODEWITHSUNDEEP
mysqldategroup-bymaxhaving
amir22
amir22

Reputation: 423

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?

Upvotes: 0

Views: 924

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

You could try selecting the customer based on the two range using main query and a subquery in left join then checking for not matching 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

Upvotes: 0

forpas
forpas

Reputation: 164099

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

Upvotes: 1

Related Questions