Subject Re[2]: [firebird-support] How do I return a set containing all records without a entry in the parent table.
Author André Knappstein, Controlling
In addition to the answer you received, in a similar situation you can
also do a left outer join and specify the missing parent (= null) in
the where clause.
I would expect this to be faster

AssistDetails a
left outer join
Entry2 e
on a.AssistEntryID = e.EntryID
e.EntryID is null



~~~Ihre Nachricht~~~

H> Replace the 'not in' by 'if not exists (select 1 from ENTRYID where
H> Entry2.ENTRYID = AssistDetails.ASSISTENTRYID'
H> will speed this up.

>> I have some sloppy code that allowed for the situation to exist, where a
>> parent record could be deleted, without a foreign key cascade delete of
>> the child records.
>> Now I need to find all these records, and delete them manually before I
>> can set up the foreign key constraint.
>> I tried:
>> Select AssistDetails.ASSISTENTRYID From AssistDetails
>> Where AssistDetails.ASSISTENTRYID Not In(Select Entry2.ENTRYID From
>> Entry2)
>> but it just hangs indefinitely.
>> Entry2 is the master table, with the Assistdetails the child table.
>> I need all AssistEntryID's with no corresponding entries in
>> Entry2.EntryID.
>> Thanks
>> Adrian
>> [Non-text portions of this message have been removed]
>> ------------------------------------
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>> Visit and click the Resources item
>> on the main (top) menu. Try Knowledgebase and FAQ links !
>> Also search the knowledgebases at
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>> Yahoo! Groups Links

H> ------------------------------------

H> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

H> Visit and click the Resources item
H> on the main (top) menu. Try Knowledgebase and FAQ links !

H> Also search the knowledgebases at

H> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
H> Yahoo! Groups Links

beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH
Hafenweg 4
59192 Bergkamen-Rünthe

Telefon: +49 2389 9240 0
Telefax: +49 2389 9240 150
e-mail: info@...

Amtsgericht Hamm Nr. B 420 || USt-IDNr.: DE 125215402
Geschäftsführer: Achim Krähling, Dirk Salewski, Matthias Steinhaus