Helen, if I run the query over one of the tables without any reference to
the 2nd one, the number of records matching the criteria is 764. When I run
your join query, I get 7834 records. I should only have the 764. The reason
I need to do this is to get the customers account no which forms part of the
original transaction. This is unfortunately the only way I can get this
info. It is for a special report. TransRef is not a primary key - it can't
be.

A query over the one table yields 764 -->

SELECT count(transref)
FROM genledger
WHERE accountid = 'Bank - Sanlam'
AND actn = 'D'
AND transdate between cast('04/01/2002' as date) and cast('04/30/2002' as
date)


The suggested join query yields 7834 -->

SELECT count(a.transref) as transcount
FROM genledger a
LEFT JOIN Genledger b on (a.transref = b.transref) and (b.actn = 'C')
WHERE a.accountid = 'Bank - Sanlam'
AND a.actn = 'D'
AND a.transdate between cast('04/01/2002' as date) and cast('04/30/2002' as
date)

The ...and (b.actn = 'C') attempts to return only the record with a 'C' for
actn making up the transaction . A transaction might look like so

> 001 30.00 'C' 1232345 -->> the one I am interested in
> 001 10.00 'D' EBFS
> 001 20.00 'D' Bank - Sanlam -->> join criteria

Many thanks,
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 · http://firebirdsql.org ·
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 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/2002




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