I have a Customers table and an Orders table. I am trying to select all of the customers' first names and email addresses who have signed up to our web site within a date range and have NOT placed an order. I can't quite get it. Any help would be appreciated. Here is what I have so far which gets me all of the customers who HAVE placed an order - but I need the opposite...

select Customers.EmailAddress, Customers.FirstName
from Customers
join Orders
on Customers.CustID = Orders.OrderID
where CAST(CONVERT(char(10), DateAdded, 112) as smalldatetime) >= '11/7/2005' and CAST(CONVERT(char(10), DateAdded, 112) as smalldatetime) <= '11/8/2005'

Are you sure on Customers.CustID = Orders.OrderID shouldn't be
on Customers.CustID = Orders.CustID ?
The query is below
Also you will probably get a table scan instead of index seek/scan with the date conversions that you are doing

select Customers.EmailAddress, Customers.FirstName
from Customers
left join Orders
on Customers.CustID = Orders.OrderID
and CAST(CONVERT(char(10), DateAdded, 112) as smalldatetime) >= '11/7/2005'
and CAST(CONVERT(char(10), DateAdded, 112) as smalldatetime) <= '11/8/2005'
where Orders.OrderID is null

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

Thanks, you're right, it should have read:
Customers.CustID = Orders.CustID

Would you recommend using "exists" for better performance?

left join and exists will likely produce the same execution plan


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

Now that I have the results I want, could you please tell me how to update this result set? Basically, I want to set a field in the Customer table to true but don't know how to do this with multiple tables selected. For example, something along these lines:

update Customers
left join Orders
on Customers.CustID = Orders.CustID
set Processed = 1
where CAST(CONVERT(char(10), DateAdded, 112) as smalldatetime) >= '11/7/2005'
and CAST(CONVERT(char(10), DateAdded, 112) as smalldatetime) <= '11/8/2005'
and Orders.OrderID is null