Hi Dion,

> Let me try again.
> My current manual method does the following:-

>1. Copy all rows having 'Bank1' as accountid and 'D' as the Actn and
>GenLedger.Transdate between '04/01/2002' and '04/30/2002'
>to a table.

>2. What I meant by 'Left Join' is 'effectively' LEFT JOIN this table with
>the complete GenLedger Table Where :-

>a. transref in the table with the extracted rows(as in 1 above) =
>transref in GenLedger AND

>b. Actn = 'C'

>I want to replace this with an SQL statement. So, effectively, I should not
>have more rows in the resulting table than there are rows meeting the
>criteria in item 1.

>ie...

>TransRef Amount Actn AccountId
>> 001 20.00 'D' Bank1
>001 30.00 'C' 1232345

>Of course, not all fields have to be selected.

IMO this must do the job (almost the same as already posted by Helen)

SELECT
a.transref,
b.amount,
b.actn,
a.accountid as accountid1,
b.accountid as accountid2
FROM
GenLedger a
LEFT JOIN GenLedger b on (b.transref = a.transref and b.Actn = 'C')
WHERE
a.accountid = 'Bank1'
and a.actn = 'D'

>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 · HYPERLINK
"http://firebirdsql.org"http://firebirdsql.org ·
HYPERLINK "http://users.tpg.com.au/helebor/"http://users.tpg.com.au/helebor/
_______________________________________________________


To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the HYPERLINK
"http://docs.yahoo.com/info/terms/"Yahoo! Terms of Service.



---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.365 / Virus Database: 202 - Release Date: 05/24/2002



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.365 / Virus Database: 202 - Release Date: 05/24/2030



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.365 / Virus Database: 202 - Release Date: 05/24/2030



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.365 / Virus Database: 202 - Release Date: 05/24/2002



[Non-text portions of this message have been removed]



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/