A SQL brain freeze

Question

I'm looking to return any affiliate who hasn't recorded any orders since a given date. Should be drop-dead simple via this query.

select * from affiliate where 
idUser not  in ( 
  select idAffiliate from Orders
  where orderDate > '06/01/11'
)

The affiliate table has a idUser field that is foreign key to the Orders table's idAffiliate. The above returns no records even though I know that I have dozens of affiliates who have no orders placed since the beginning of this month. If I change the date to '07/01/11' - all the affiliate records return (obviously) but verifies I'm using correct entity names if nothing else.

much appreciated

Answer 1

Using a left join:

select a.* 
from affiliate a
left join Orders o
    on a.idUser= o.idAffiliate
    and o.orderDate > '06/01/11'
where o.idAffiliate is null

Answer 2

Looks like you must change idAffiliate to idUser in nested query. And better use EXISTS or NOT EXISTS instead of IN in such cases

select * from affiliate a 
where not exists ( 
  select 1 from Orders where orderDate > '06/01/11'
  and Orders.idUser = a.idUser
)