Hmmmmm...well this query doesn't seemed to be all that much faster
than the sub-select. And definitely no where near the speed of the
join. Yes, there is an index on F_ID.

> Delete Trigger to say something like:
>
> delete from parent
> where f_child_id = old.f_id
> and f_foo = 1

Do you mean this?

delete from Child where Child.F_ID = OLD.F_ID;

Also, it may not be obvious yet but there are MANY Child records to a
single Parent record.

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@y...> wrote:
>
>
> In simple english,
>
> Main Query:
>
> delete any record from the child where the sub query returns at least
> one record.
>
> SubQuery:
>
> Return the value 1 every time you come across a record in the parent
> with a child ID equal to the current child we are checking, and the
> parent f_foo equals 1.
>
> Helen will have to tell you if the following query would provide any
> benefit:
>
> DELETE FROM Child c
> WHERE EXISTS(
> SELECT FIRST 1 1 FROM Parent p
> WHERE p.F_CHILD_ID = c.F_ID AND p.F_FOO = 1)
>
> I have added the 'first 1' directive to say only return the first
> record. It is logically equivalent but just might save firebird some
work.
>
> In either case, I am sure you have a reason not to, but isn't it
> standard to define a foreign key if you are going to join on that
> record, or if that is not possible, check that your tables are
> normalised. I am also not sure why you don't want to use the Before
> Delete Trigger to say something like:
>
> delete from parent
> where f_child_id = old.f_id
> and f_foo = 1
>
> Also, make sure you have at least have an index (except if you have a
> foreign key constraint which will add one for you).
>
>
>
> --- In firebird-support@yahoogroups.com, "robert_difalco"
> <rdifalco@t...> wrote:
> >
> > --- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
> > wrote:
> > >
> > > DELETE FROM Child c
> > > WHERE EXISTS(
> > > SELECT 1 FROM Parent p
> > > WHERE p.F_CHILD_ID = c.F_ID AND p.F_FOO = 1)
> > >
> >
> > Helen, could you explain how this query works? Is it standard SQL that
> > can be used across different database server?
> >
> > I don't think understand exactly how this is parsed and how the
> > relation between Parent and Child is made here. The JOIN I understand,
> > even the Sub-Select I get, but I don't grok this one.
> >
> > TIA,
> >
> > R.