| 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 |
| Post date | 2008-12-01T10:50:31Z |
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
Select
a.AssistEntryID
from
AssistDetails a
left outer join
Entry2 e
on a.AssistEntryID = e.EntryID
where
e.EntryID is null
ciao,
André
--
~~~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.
H> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
H> Visit http://www.firebirdsql.org and click the Resources item
H> on the main (top) menu. Try Knowledgebase and FAQ links !
H> Also search the knowledgebases at http://www.ibphoenix.com
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
also do a left outer join and specify the missing parent (= null) in
the where clause.
I would expect this to be faster
Select
a.AssistEntryID
from
AssistDetails a
left outer join
Entry2 e
on a.AssistEntryID = e.EntryID
where
e.EntryID is null
ciao,
André
--
~~~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.
>>H> ------------------------------------
>> 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 http://www.firebirdsql.org and click the Resources item
>> on the main (top) menu. Try Knowledgebase and FAQ links !
>>
>> Also search the knowledgebases at http://www.ibphoenix.com
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>> Yahoo! Groups Links
>>
>>
>>
>>
H> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
H> Visit http://www.firebirdsql.org and click the Resources item
H> on the main (top) menu. Try Knowledgebase and FAQ links !
H> Also search the knowledgebases at http://www.ibphoenix.com
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