Subject Re: [ib-support] Join Table with self
Author Helen Borrie
At 04:20 PM 10-06-02 +0200, you wrote:
>Is it possible to join a table with itself as follows:-
> SELECT * FROM GenLedger a
> JOIN GenLedger b ON a.TransRef = b.TransRef
> AND a.Actn='C'

Well, yes, self-joins are perfectly possible, but why do you think you need
a self-join for this dataset?

Why not just
SELECT * FROM GenLedger where Actn='C'


If this were a join, it would be invalid syntax, since this clause

AND a.Actn='C'

is not a join criterion, but a WHERE criterion, i.e. it ought to be

WHERE a.Actn='C'

However, the joined query is a) pointless and b) endless

You typically use a self-join to denormalise an hierarchical structure,
where one column in the table is the "parent" of the primary key or another
unique key.

> I run out of memory when I run this query.

I suspect there is something you haven't told us about this query.


All for Open and Open for All
Firebird Open SQL Database · ·