Multiple ways heres one:
SELECT CustID
FROMCustomers
WHERE CustID NOT IN (Select CustID From Orders)
Adam
On Tue, 30 Nov 2004 12:01:30 -0500, Che Vilnonis [EMAIL PROTECTED] wrote:
Say I have two simple queries:
cfquery name=getCustsFromCusts datasource=#DSN#
SELECT CustID
FROM
How about this?
Select CustID from customers where custID not in (select custid from
orders)
-dov
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 12:01 PM
To: CF-Talk
Subject: SQL Query question. Please help...
Say I have two simple
Sometimes using NOT can have performance issues
You can also do:
SELECT a.CustID
FROMCustomers a,Orders b
WHERE a.CustID = b.CustID
-Original Message-
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 11:06 AM
To: CF-Talk
Subject: Re: SQL Query question
, November 30, 2004 11:05 AM
To: CF-Talk
Subject: RE: SQL Query question. Please help...
Sometimes using NOT can have performance issues
You can also do:
SELECT a.CustID
FROMCustomers a,Orders b
WHERE a.CustID = b.CustID
-Original Message-
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent
-Original Message-
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 11:06 AM
To: CF-Talk
Subject: Re: SQL Query question. Please help...
Multiple ways heres one:
SELECT CustID
FROMCustomers
WHERE CustID NOT IN (Select CustID From Orders)
Adam
On Tue, 30
You can also do:
SELECT a.CustID
FROMCustomers a,Orders b
WHERE a.CustID = b.CustID
-Original Message-
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 11:06 AM
To: CF-Talk
Subject: Re: SQL Query question. Please help...
Multiple ways
Adam/Dov
Using your query my recordcount is off.
Actual # of customer ids from my customers table = 865.
Actual # of customer ids from my orders table = 596.
Using your query, the difference = 335.
865 - 596 should = 269. What am I missing? Debug info is below.
Under your math you are assuming a 1 to 1 relationship between orders
and Custromers. Unless you are running a scam, where no customer would
ever by from you twice, that is not case, the whole reason you have 2
seperate tables. Hope thats makes sense.
Adam H
On Tue, 30 Nov 2004 12:18:23 -0500,
You have multiple Orders for some customers.
What is count for:
SELECT DISTINCT Custid from Orders
-Original Message-
From: Che Vilnonis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 12:18 PM
To: CF-Talk
Subject: RE: SQL Query question. Please help...
Adam/Dov
I see. Haven't had my lunch yet. Thanks for your help.
Che
-Original Message-
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 12:23 PM
To: CF-Talk
Subject: Re: SQL Query question. Please help...
Under your math you are assuming a 1 to 1 relationship
At 12:22 PM 11/30/2004, Adam Haskell wrote:
Under your math you are assuming a 1 to 1 relationship between orders
and Custromers. Unless you are running a scam, where no customer would
ever by from you twice, that is not case, the whole reason you have 2
seperate tables. Hope thats makes sense.
11 matches
Mail list logo
