At 04:04 PM 12-06-02 +0200, you wrote:

>Hi,
>
>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.

Much better. :))

select a.transref, b.amount, b.actn,
a.accountid as accountid1,
b.accountid as accountid2
from ATable a
left join ATable b
on a.transref = b.transref
where a.accountid = 'Bank1'
and b.actn = 'C'

This will give you one row for each occurrence of accountid = 'Bank1' in
the entire table. Those that have 'C' rows with matching transref will be
complete; those that don't will have nulls in all the b.columns.

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________