Help with Query

Hi,
Am trying to work out how to perform a query. I have a table which contains
finance cost codes (tblCostCodes). I have another table that contains
Customers and the Cost Codes used by that Customer (tblCustomerCostCodes) -
they are related by the CostCode field. What I want is to be able to extrac
t
the values in the first table only when the values don't appear in the
second. Not sure how to do this.
--
Many thanks in advance for any assistanceSelect * from tblCostCodes C
Where Not Exists (Select * From tblCustomerCostCodes
Where CostCode = C.CostCode )
"Marek" wrote:

> Hi,
> Am trying to work out how to perform a query. I have a table which contai
ns
> finance cost codes (tblCostCodes). I have another table that contains
> Customers and the Cost Codes used by that Customer (tblCustomerCostCodes)
-
> they are related by the CostCode field. What I want is to be able to extr
act
> the values in the first table only when the values don't appear in the
> second. Not sure how to do this.
> --
> Many thanks in advance for any assistance|||That worked a treat - thanks for that
"CBretana" wrote:
> Select * from tblCostCodes C
> Where Not Exists (Select * From tblCustomerCostCodes
> Where CostCode = C.CostCode )
>
> "Marek" wrote:
>|||Yr welcome !
"Marek" wrote:
> That worked a treat - thanks for that
> "CBretana" wrote:
>|||This one should be faster.
Select * from tblCostCodes CC
Left join tblCustomerCostCodes CCC on CCC.CostCode = CC.CostCode
Where CCC.CostCode is null
JN.
"CBretana" <cbretana@.areteIndNOSPAM.com> a crit dans le message de news:
53A0FAE0-26BF-46D8-9CF6-FF195C3FF9A4@.microsoft.com...
> Select * from tblCostCodes C
> Where Not Exists (Select * From tblCustomerCostCodes
> Where CostCode = C.CostCode )
>
> "Marek" wrote:
>|||Why would you say that? I think, internally, the optimizer will do the same
thing, or very close. An experiment I ran with a small resultset, showing
Execution Plan, yielded yours having 51% of the workload and the previous
version at 49%. The important thing to note was that both had two table
scans, more importantly there were deltas here. The NOT EXISTS version had
one with a row count of 5 and one with a row count of 2. (I inserted 5 rows
into CostCodes and 2 rows into CustomerCostCodes.) Your version had one
with a row count of 5, and one with a row count of 10! You also return a
second column for nothing (I suggest you both get out of the habit of
using/recommending SELECT *). So you can imagine where this is going to go
as the size of the tables goes up...
On 3/12/05 6:00 PM, in article OkLMTd1JFHA.732@.TK2MSFTNGP12.phx.gbl,
"Jean-Nicolas BERGER" <j-n.enlevezmoi.berger@.club-internet.fr> wrote:

> This one should be faster.
> Select * from tblCostCodes CC
> Left join tblCustomerCostCodes CCC on CCC.CostCode = CC.CostCode
> Where CCC.CostCode is null
> JN.
> "CBretana" <cbretana@.areteIndNOSPAM.com> a crit dans le message de news:
> 53A0FAE0-26BF-46D8-9CF6-FF195C3FF9A4@.microsoft.com...
>|||I Agree with Aaron,
In General, most of the tme, when I have used showplan to compare using
correlated subquerys with joins, the optimizer laughs at me, and does the
same thing for both... So I generally now opt for the syntax that makes the
logic the most clear...
"Jean-Nicolas BERGER" wrote:

> This one should be faster.
> Select * from tblCostCodes CC
> Left join tblCustomerCostCodes CCC on CCC.CostCode = CC.CostCode
> Where CCC.CostCode is null
> JN.
> "CBretana" <cbretana@.areteIndNOSPAM.com> a écrit dans le message de news:
> 53A0FAE0-26BF-46D8-9CF6-FF195C3FF9A4@.microsoft.com...
>
>|||You're right.
Even for big tables, the execution plan is the same.
Internally, the optimizer thranforms the "if exists" so it looks like a
Join.
(I've tried with tables with 1 000 000 rows)
JN.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le message de
news: BE58E344.2EF6%ten.xoc@.dnartreb.noraa...
> Why would you say that? I think, internally, the optimizer will do the
> same
> thing, or very close. An experiment I ran with a small resultset, showing
> Execution Plan, yielded yours having 51% of the workload and the previous
> version at 49%. The important thing to note was that both had two table
> scans, more importantly there were deltas here. The NOT EXISTS version had
> one with a row count of 5 and one with a row count of 2. (I inserted 5
> rows
> into CostCodes and 2 rows into CustomerCostCodes.) Your version had one
> with a row count of 5, and one with a row count of 10! You also return a
> second column for nothing (I suggest you both get out of the habit of
> using/recommending SELECT *). So you can imagine where this is going to
> go
> as the size of the tables goes up...
>
> On 3/12/05 6:00 PM, in article OkLMTd1JFHA.732@.TK2MSFTNGP12.phx.gbl,
> "Jean-Nicolas BERGER" <j-n.enlevezmoi.berger@.club-internet.fr> wrote:
>
>