Just for the record, EXISTS can be used in Access, too.
Joe, I think the SQL statement Samuel provided would be your best bet for performance.
In fact, it's probably safe to say that a JOIN will generally perform better than
EXISTS and EXISTS will generally perform better than IN.
~Dina
----- Original Message -----
From: Dina Hess
To: CF-Talk
Sent: Friday, January 10, 2003 2:07 PM
Subject: Re: Help with a query.
If you're using SQL Server, you could rewrite your query like this:
SELECT s.shopperID
FROM tblShopper s
WHERE NOT EXISTS (SELECT * FROM tblorder o where o.shopperID = s.shopperID)
~Dina
----- Original Message -----
From: Eric Creese
To: CF-Talk
Sent: Friday, January 10, 2003 10:15 AM
Subject: Help with a query.
I would do the following
SELECT tblShopper.shopperID
FROM tblShopper
WHERE shopperID NOT IN (SELECT DISTINCT tblOrder.shopperid FROM tblorder)
Not sure what DB your using but using a NOT and or a DISTINCT can slow
performance. Also your distinct appeared to be in the wrong place, I am guessing the
tblShopper.shopperid is unique so a DISTINCT there will not help you, however using it
in the subquery makes sense since a sopper could have multiple orders. Truthfully
though you could eliminate it from the subquery as well.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
