Title: Message
|
This
works, but I think you have the check the wrong way round Paul. Eric wants
to know the entries in the Sub table with no matching Master
entry.
Note that if the tables are large the NOT IN is very
inefficient.
I had
a case where this type of query was running for more than 1 day before we
stopped it. The following is much faster (SQL Server
syntax)
SELECT ID
INTO #tmp
FROM Sub
DELETE FROM t
FROM #tmp t, Master m
WHERE t.ID = m.ID
SELECT ID
FROM #tmp
DROP TABLE #tmp
In our
case this dropped the query from 1+ day to 3 seconds
Stephen
This should do it...
select * from Master MT
where not exists
(select ST.ID from Sub ST where ST.ID = MT.ID)
HTH
Regards
Paul McKenzie
Analyst Programmer
SMSS
Ltd.
----- Original Message -----
Sent: Friday, March 19, 2004 9:46
AM
Subject: [DUG] Programming in Delphi,
but have a SQL question
I am trying to implement a new report
in Delphi, but I am having problems with the query. I know this is not a
straight Delphi question, but I was hoping someone here could steer me in
the right direction.
I have two tables with transactions.
One table contains ALL transactions, the second table is a subset of the
first with only a specific set of transactions. The columns differ between
the two tables, but there is enough relational information for me to match
all of the subset transactions with the master transaction table.
In
some odd cases the subset transaction is inserted, but the corresponding
entry is not added to the master transaction table.
I need to write a
query that shows which transactions in the subset table do not appear in the
master table.
Can someone help me with
this?
Thanks,
Eric
Eric Tishler
Software Architect
Resolute Partners,
LLC
Phone: 203.271.1122
Fax: 203.271.1460
[EMAIL PROTECTED]
_______________________________________________
Delphi mailing
list
[EMAIL PROTECTED]
http://ns3.123.co.nz/mailman/listinfo/delphi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This communication contains information that is confidential and the copyright of ensynergy Limited or a third party.
If you are not the intended recipient of this communication please delete and destroy all copies and telephone ensynergy Limited on +64 9 3551591 immediately. If you are the intended recipient of this communication you should not copy, disclose or distribute this communication without the authority of ensynergy Limited.
Any views expressed in this communication are those of the individual sender, except where the sender specifically states them to be the views of ensynergy Limited.
Except as required by law, ensynergy Limited does not represent, warrant and/or guarantee that the integrity of this communication has been maintained nor that the communication is free of errors, virus, interception or interference.
|
_______________________________________________
Delphi mailing list
[EMAIL PROTECTED]
http://ns3.123.co.nz/mailman/listinfo/delphi
