Subject Re: [ib-support] Join Table with self
Author Jason Chapman (JAC2)
""Arno Brinkman"" <firebird@...> wrote in message
> Hi,
> > I would probably do this as
> > Select * from GenLedger where Tranref in (select Tranref from GenLedger
> when
> > Actn = 'C') Especially if Actn does not have an index on it.
> >
> > What do the two different Q's return as a plan?
> >
> > JAC
> Isn't that the same as :
> Select * from GenLedger where Actn = 'C'

Only if Tranref is a primary key, which it isn't as far as I understand the
original question. My Q reuturns all lines of any transactions that have a
Actn = 'C' in one or mor rows of the transaction.

> but without the very slow IN statement

Do you know it is slow, or you think it should be slow? In an ideal world
it isn't a very elegant solution, but works well with IB5.6, 5.6.1 & 5.7
with millions of rows in a table where the inner select uses efficient
indices and returns < 50K records. Just my findings.