0

I'm know this can be written as a single SQL statement, but I just don't know how to do it. I have two separate queries. Ont that pulls all orders from a specific period of last year 

SELECT * FROM `order` WHERE date_added BETWEEN '2014-10-01' AND '2014-11-01';

and one that pulls from the last month

SELECT * FROM `order` WHERE date_added BETWEEN DATE_SUB( now(), INTERVAL 1 MONTH) AND Now() ORDER BY date_added ASC

What I want to do is now join the two so that I only get the customer_id of orders that were placed inside of the date range last year (query 1), but haven't placed an order in the last month (query 2).

I know there is a way to set this up as a join, but my knowledge on sql join's is not very limited. Thanks for any help.

user3167249
  • 1,082
  • 2
  • 14
  • 28

3 Answers3

1

I usually use a correlated not exists predicate for this as I feel that it corresponds well with the intent of the question:

SELECT * 
FROM `order` o1
WHERE date_added BETWEEN '2014-10-01' AND '2014-11-01'
  AND NOT EXISTS (
    SELECT 1 
    FROM `order` o2
    WHERE date_added BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW() 
      AND o1.customer_id = o2.customer_id
);
jpw
  • 44,361
  • 6
  • 66
  • 86
  • This worked good. Couple questions. what is the 1 for in the second select? I was confused by that also could I add Distinct to the first select to only get unique Customer ID's? – user3167249 Oct 12 '15 at 16:48
  • @user3167249 The `1` is just used to signify that the result returned from the subquery isn't important as the `exists` predicate only evaluates if any result is returned, not what it is. You could just as well use `*` or some column. I just think it's clearer this way. As for the `distinct` - yes, if you only want unique customers you can do `select distinct o1.customer_id` instead for example. – jpw Oct 12 '15 at 16:51
1

http://sqlfiddle.com/#!9/35ed0/1

SELECT * FROM `order` 
WHERE date_added BETWEEN '2014-10-01' AND '2014-11-01'
   AND customer_id NOT IN (
   SELECT DISTINCT customer_id  FROM `order` 
   WHERE date_added BETWEEN DATE_SUB( now(), INTERVAL 1 MONTH) AND Now())

UPDATE If you need only 1 records per customer_id, here is an example . It is not very best from performance perspective. But it returns only last (according to the date_added column) order per customer.

SELECT t.*,
   if(@fltr=customer_id, 0, 1) fltr,
   @fltr:=customer_id
FROM (SELECT *
  FROM `order` 
  WHERE date_added BETWEEN '2014-10-01' AND '2014-11-01'
    AND customer_id NOT IN (
     SELECT DISTINCT customer_id  FROM `order` 
     WHERE date_added BETWEEN DATE_SUB( now(), INTERVAL 1 MONTH) AND Now())
  ORDER BY customer_id, date_added DESC
) t
HAVING (fltr=1);
Alex
  • 16,739
  • 1
  • 28
  • 51
  • This works well and is very concise. How does NOT IN compare to NOT EXIST? – user3167249 Oct 12 '15 at 16:58
  • that is almost no difference on your case. I like `NOT IN`, it seems more clean and logical to me. – Alex Oct 12 '15 at 17:00
  • I noticed this actually produces duplicates of the same customer ID if there is more than one match. Does the DISTINCT not work to narrow this down to only unique customer_id's? – user3167249 Oct 12 '15 at 17:01
  • If you just need distinct customer_id. change `SELECT *` to `SELECT DISTINCT customer_id`. I did `*` becuase you wrote in your OP that firs query was `SELECT *` – Alex Oct 12 '15 at 17:02
  • With MySQL `not in` should perform slightly better than `not exists` according to [this old answer](http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null?lq=1) (which might be outdated). – jpw Oct 12 '15 at 17:03
  • I would like to select the whole table * but only include 1 result per customer_id. So if they placed 3 orders last year but none in the past month they only get added once. – user3167249 Oct 12 '15 at 17:09
0

I like to approach these questions using group by and having. You are looking for customer ids, so:

select o.customer_id
from orders o
group by o.customer_id
having sum(date_added BETWEEN '2014-10-01' AND '2014-11-01') > 0 and
       sum(date_added BETWEEN DATE_SUB( now(), INTERVAL 1 MONTH) AND Now()) = 0;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786