Title: Message
Thank you for the explanation
Best Regards,
Eric
Eric Tishler
Software Architect
Resolute Partners, LLC
Phone: 203.271.1122
Fax: 203.271.1460
[EMAIL PROTECTED]
-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On
Behalf Of Paul
Title: Message
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
Title: Message
How about a sub-select eg.
Select * from sub_trans_table where trans_pk
not in (select trans_pk from all_trans_table)
HTH
Chris
--
Chris Milham BSc BD
Senior Software Engineer
EVP Holdings Ltd
DDI: + 64 9 526 2423
Fax: + 64 9 571 0017
www.evp.cc
The information
Title: Message
This should do it...
select * from Master MTwhere not exists (select
ST.ID from Sub ST where ST.ID = MT.ID)
HTH
RegardsPaul McKenzieAnalyst ProgrammerSMSS Ltd.
- Original Message -
From:
Eric Tishler
To: NZ Borland Developers Group - Delphi
List
I would tend to use an outer join where the JOINed master column is NULL eg
SELECT Subset.*
FROM Subset
LEFT OUTER JOIN Master ON Subset.ID = Master.ID
WHERE Master.ID IS NULL
cheers,
Paul.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Behalf Of Eric Tishler
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.
Notethat if the tables are large the NOT IN is very
inefficient.
I had
a case where this type of query was running for more
Title: Message
SELECT *
FROM Subset
LEFT JOIN Master ON Master.Pk =
Subset.Pk
WHERE Master.PK IS NULL;
It might be a right join, I always get it
mixed up.
Rob MartinSoftware Engineerphone 03 377 0495fax 03
377 0496 web www.chreos.com
- Original Message -
From:
Eric
Agreed. I think using a JOIN method is faster than doing a sub select
(might be significant for a large data set).
Rob Martin
Software Engineer
phone 03 377 0495
fax 03 377 0496
web www.chreos.com
- Original Message -
From: Paul Ritchie [EMAIL PROTECTED]
To: 'NZ Borland Developers
I will have to consider trying the JOIN method, but I got it working with NOT EXISTS.
For some reason my dialect of SQL did not lie NOT IN.
Thanks for everyone's help ...
Eric
Eric Tishler
Software Architect
Resolute Partners, LLC
Phone: 203.271.1122
Fax: 203.271.1460
[EMAIL PROTECTED]
