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

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;

CodePudding user response：

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

CodePudding user response：

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'