Hi All,

Let me try again.

Table GenLedger

=============

TransRef    Amount   Actn        AccountId

> 001          30.00         'C'             1232345
> 001         10.00          'D'            EBFS
> 001         20.00         'D'            Bank1

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.

Thanks for your patience.

Dion.

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Wednesday, June 12, 2002 4:42 PM
To: ib-support@yahoogroups.com
Subject: RE: [ib-support] Join Table with self


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 · 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]