Dion,

I assume you expect only one 'C' journal to match your 'D' journal. It looks
suspiciously like you may have more than one 'C' journal entry for some of
the 'D' journals. You're doing a left join which implies that you may have
zero 'C' journals for some 'D's - is that right? If not then remove the
'LEFT' clause.


Vince

----- Original Message -----
From: Dion Oliphant <dion@...>
To: <ib-support@yahoogroups.com>
Sent: Thursday, 13 June, 2002 4:04 PM
Subject: RE: [ib-support] Join Table with self


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]



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/