Where no record exists between two dates in a joined table

I'm trying to select all of our items that have not sold at all in the past 2 months.

I'm trying to use this query, but it's not working 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 of the records from the Items table (grouped by item id), if and only if they have not been ordered within the past two months.

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

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

My result should return only Widget A, since it was not ordered in the last 2 months. The fact that is was ordered over a year ago is not relevant.

Widget C should not appear because an order containing a Widget C was placed in the past 2 months.

The problem is, the records I want won't have a date range associa开发者_高级运维ted with them. Another way to word it is:

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

How can I get that?

Personally, I think this is 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 in question).

You can also write it this way:

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

Either version should be "fast enough" over a few thousand items for occasional use. If your item table is much bigger or you need to perform this query during some kind of online transaction (as opposed to reporting) there are alternative ways to write it. You can even do it with a JOIN, but you only need to join orders once, not twice.

(Note: You have two different definitions of the question in your request. First, you're asking for all items not sold in the last two months. Then, in the summary, you're asking for all items that have sold at least once but not in the last two months. The first version (which I answered) will include items never sold at all. The second excludes them.)