Hi,

select a.accountid, b.accountid, a.transref, a.actn from GenLedger a,
GenLedger b
Where a.accountid='Bank - Sanlam'
and a.transref = b.transref
and a.genledgerid <> b.genledgerid
and (a.actn = 'D' and b.actn = 'C')
and a.transdate between cast('04/01/2002' as date) and cast('04/30/2002'
as date)
and b.transdate between cast('04/01/2002' as date) and cast('04/30/2002'
as date)

Effectively, I want GenLedger a to contain the following records:-

select * from GenLedger a
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)

and then have this result set merged(joined) with the total table
itself(GenLedger b). I am only interested in the the joined records from
GenLedger a.

Currently I am extracting GenLedger a from the whole table, iterating
through the extracted result, looking up the corresponding record which has
the same
transref and a 'C' for actn in the total table and copying this to another
table. This is time consuming.

Many Thanks,
Dion.

ps would a better solution be a SP?



-----Original Message-----
From: Thomas Miller [mailto:tmiller@...]
Sent: Monday, June 10, 2002 4:33 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Join Table with self


Yes

Your From part needs to look like this

FROM
GenLedger A ,
GenLedger B

Dion Oliphant wrote:

> Hi,
>
> Is it possible to join a table with itself as follows:-
>
> SELECT * FROM GenLedger a
> JOIN GenLedger b ON a.TransRef = b.TransRef
> AND a.Actn='C'
>
> I run out of memory when I run this query.
>
> Thanks,
> Dion.
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> <http://www.grisoft.com%29.>
> Version: 6.0.365 / Virus Database: 202 - Release Date: 05/24/2002
>
>
>
> *Yahoo! Groups Sponsor*
> ADVERTISEMENT
>

<http://rd.yahoo.com/M=226014.2032696.3508022.1829184/D=egroupweb/S=17051153
86:HM/A=1000239/R=0/*http://ads.x10.com/?bHlhaG9vaG0xLmRhd=1023719491%3eM=22
6014.2032696.3508022.1829184/D=egroupweb/S=1705115386:HM/A=1000239/R=1>

>
>
>
> 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
> <http://docs.yahoo.com/info/terms/>.

--
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com




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