Find user ID's who appear in a certain period and not in another

Question

Given a table tbl_orders containing order details for many months, what's the most efficient SQL query to get a list of user ID's userID who appear in a given period BETWEEN 2019-01-01 AND 2019-01-31 but don't appear in another period BETWEEN 2019-02-01 AND 2019-02-28.

Date column is orderDate

I tried self join but it returns nothing

SELECT DISTINCT a.userID
FROM tbl_orders a,
     tbl_orders b
WHERE a.orderDate BETWEEN 2019-01-01 AND 2021-01-31
  AND b.orderDate
    NOT BETWEEN 2019-02-01 AND 2019-02-28;

When you don't put the dates into quotes , `MySQL` is doing arithmetic operations so `2019-01-01` is equal to `2017` and `2021-01-31` equal to `1989` . So you are doing `WHERE a.orderDate BETWEEN 2017 AND 1989` — Ergest Basha, Dec 04 '21 at 13:58

ProDec · Answer 1 · 2021-12-04T13:54:43.347

1

Possibly this one:

SELECT userID
FROM tbl_orders
WHERE orderDate BETWEEN '2019-01-01' AND '2019-02-28'
GROUP BY userID
HAVING MAX(orderDate) <= '2019-01-31'

edited Dec 04 '21 at 13:54

answered Dec 04 '21 at 13:43

ProDec

5,390
1
3
12

score 1 · Accepted Answer · answered Dec 04 '21 at 13:45

You can use NOT EXISTS

Your dates are wrong the must have a single quote

SELECT DISTINCT a.userID
FROM tbl_orders a 
WHERE a.orderDate BETWEEN '2019-01-01' AND '2021-01-31'
  AND NOT EXISTS(SELECT 1 FROM tbl_orders WHERE orderDate
    BETWEEN '2019-02-01' AND '2019-02-28');

Find user ID's who appear in a certain period and not in another

2 Answers2