Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 10th, 2005, 01:38 PM
Registered User
 
Join Date: Sep 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Nested Query or Join?

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'
 
Old November 10th, 2005, 02:08 PM
Authorized User
 
Join Date: Sep 2005
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
 
Old November 10th, 2005, 02:30 PM
Registered User
 
Join Date: Sep 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Would you recommend using "exists" for better performance?
 
Old November 10th, 2005, 02:33 PM
Authorized User
 
Join Date: Sep 2005
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
 
Old November 10th, 2005, 05:23 PM
Registered User
 
Join Date: Sep 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested query deb1980 ASP.NET 2.0 Basics 2 April 10th, 2007 04:38 AM
JOIN query wverner Access 2 April 5th, 2005 01:44 PM
Nested Query teclo MySQL 1 March 22nd, 2005 07:20 AM
Nested Query BrianWren Oracle 5 February 1st, 2005 01:45 PM
Join Query tsimsha Classic ASP Databases 2 December 2nd, 2004 09:31 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.