| Subject | RE: [ib-support] Join Table with self |
|---|---|
| Author | Dion Oliphant |
| Post date | 2002-06-12T14:04:42Z |
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.
�
Many Thanks,
Dion.
-----Original Message-----
From: Vince Duggan [mailto:vince@...]
Sent: Tuesday, June 11, 2002 1:35 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Join Table with self
Dion,
The self join is fine, except for one or two points. The line:
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
�
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.
�
Many Thanks,
Dion.
-----Original Message-----
From: Vince Duggan [mailto:vince@...]
Sent: Tuesday, June 11, 2002 1:35 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Join Table with self
Dion,
The self join is fine, except for one or two points. The line:
>�� and a.genledgerid <> b.genledgeridis 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 ( HYPERLINK
"http://www.grisoft.com)."http://www.grisoft.com).
>�� > < HYPERLINK "http://www.grisoft.com%29."http://www.grisoft.com%29.>
>�� > Version: 6.0.365 / Virus Database: 202 - Release Date: 05/24/2002
>�� >
>�� >
>�� >
>�� > *Yahoo! Groups Sponsor*
>�� > ADVERTISEMENT
>�� >
>
< HYPERLINK
"http://rd.yahoo.com/M=226014.2032696.3508022.1829184/D=egroupweb/S=17051153
"http://rd.yahoo.com/M=226014.2032696.3508022.1829184/D=egroupweb/S=17051153
>
86:HM/A=1000239/R=0/* HYPERLINK
"http://ads.x10.com/?bHlhaG9vaG0xLmRhd=1023719491%3eM=22"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
>�� > < HYPERLINK
"http://docs.yahoo.com/info/terms/"http://docs.yahoo.com/info/terms/>.
>
>
>�� --
>�� Thomas Miller
>�� Delphi Client/Server Certified Developer
>�� BSS Accounting & Distribution Software
>�� BSS Enterprise Accounting FrameWork
>
>�� HYPERLINK "http://www.bss-software.com"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 ( HYPERLINK
"http://www.grisoft.com)."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 ( HYPERLINK
"http://www.grisoft.com)."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 HYPERLINK
"http://docs.yahoo.com/info/terms/"http://docs.yahoo.com/info/terms/
>
>
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/2002
[Non-text portions of this message have been removed]