Hi,

> 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'

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

>All records forming a transaction are linked together via a field called
>transref.
>ATable
>transref amount actn accountid
>001 30.00 'C' 1232345
>001 10.00 'D' EBFS
>001 20.00 'D' Bank1
>What I want is all records = 'Bank1' with the corresponding 'C' linked via
>the transref. Effectively what I want to do is a left join of a table on

its

>self for a select record value. I want the accountid1,
>accountid2 values in the same row(ie the association) done via SQL.
>The result I want from the above table is:-
>transref amount actn accountid1 accountid2
>001 30.00 'C' Bank1 1232345

>I hope this explains my needs.

When i understand you your first query was almost good except the join part.
Try this (untested ofcourse):

SELECT
a.Transfer,
b.Amount,
b.Actn,
a.Accountid,
b.Accountid
FROM
GenLedger a
JOIN GenLedger b ON (a.TransRef = b.TransRef AND b.Actn='C')
WHERE
a.accountid = 'Bank1'

Regards,
Arno