Dion,

The self join is fine, except for one or two points. The line:

> and a.genledgerid <> b.genledgerid

is probably not required since the next line:

> and (a.actn = 'D' and b.actn = 'C')

will ensure that you are not joining a row to itself.

Putting in 'not equal' conditions in a WHERE statement is normally the cause
of slow queries. Try looking at the PLAN with and without the '<>' condition
and see if there is any difference.

A Stored Procedure can improve the speed of this sort of thing dramatically,
since the where condition is simpler, you often get to the right records
quicker.

You say you run out of memory. What client are you using?

Vince

Virgin Active
Kenilworth
Cape Town

----- Original Message -----
From: Dion Oliphant <dion@...>
To: <ib-support@yahoogroups.com>
Sent: Tuesday, 11 June, 2002 10:08 AM
Subject: RE: [ib-support] Join Table with self


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