If there is no record between two dates in the joined table

Question

I am trying to select all of our items that have not been sold at all in the last 2 months.

I am trying to use this query, but it does not work as expected:

SELECT SalesDescription FROM Items I LEFT JOIN Orders_Items OI ON OI.ItemID=I.ItemID LEFT JOIN Orders O ON O.OrderID=OI.OrderID WHERE OrderTime NOT BETWEEN date_sub(curdate(), interval 2 month) AND date_sub(curdate(), interval 1 day) Group By I.ItemID 

Basically, I want to get all the entries from the Items table (grouped by item ID) if and only if they have not been ordered in the last two months.

When I do my join above, the resulting table looks something like this:

 Name OrderID OrderDate Widget A 1 Last Year Widget B 2 Last Week Widget C 3 Last Year Widget C 4 Last Week 

Only Widget A should return my result, since it has not been ordered in the last 2 months. The fact that it was ordered more than a year ago is not relevant.

Widget C should not appear because an order containing Widget C has been placed in the last 2 months.

The problem is that the records I want will not have a date range associated with them. Another way to express this:

I want to start with all the items in the Items table, and then exclude those that have orders, and at least one of these attached orders was placed within a 2-month range.

How can i get this?

Answer 1

Personally, I consider this the clearest expression of your question:

  SELECT SalesDescription FROM Items I WHERE NOT EXISTS (SELECT * FROM Orders O WHERE O.ItemID = I.ItemID AND O.OrderTime BETWEEN X AND Y) 

(where X and Y are the dates indicated).

You can also write it like this:

  SELECT SalesDescription FROM Items WHERE ItemID NOT IN (SELECT ItemID FROM Orders O WHERE O.OrderTime BETWEEN X AND Y) 

Any version should be "fast enough" for several thousand items for random use. If the product table is much larger or you need to fulfill this request during an online transaction (as opposed to reports), there are alternative ways to write it. You can even do this with JOIN, but you only need to join the order once, not twice.

(Note. You have two different definitions of the question in your request. First, you ask for all items that have not been sold in the last two months. Then, in the resume, you request all items that have been sold at least once, but not in the last two months. The first version (to which I replied) will include items that have never been sold at all, and the second will exclude them.)