sql server mseq: From an earlier post, I was informed one way to find a
common set of values in a table is with a self join. Here
is a query I constructed with many self joins:


select distinct a1.report_byperiod
from #Acct_info a1, #Acct_info a2, #Acct_info a3,
#Acct_info a4, #Acct_info a5, #Acct_info a6, #Acct_info
a7, #Acct_info a8, #Acct_info a9, #Acct_info a10,
#Acct_info a11, #Acct_info a12, #Acct_info a13, #Acct_info
a14,
#Acct_info a15, #Acct_info a16, #Acct_info a17, #Acct_info
a18, #Acct_info a19, #Acct_info a20, #Acct_info a21,
#Acct_info a22, #Acct_info a23, #Acct_info a24, #Acct_info
a25,
#Acct_info a26, #Acct_info a27, #Acct_info a28, #Acct_info
a29, #Acct_info a30, #Acct_info a31, #Acct_info a32,
#Acct_info a33, #Acct_info a34,
#Acct_info a35, #Acct_info a36, #Acct_info a37
where a1.account_number = 'P12345' and a2.account_number
= 'P12346' and a3.account_number = 'P12347' and
a4.account_number = 'P12348'
and a5.account_number = 'P12349' and a6.account_number
= 'P12310' and a7.account_number = 'P12311' and
a8.account_number = 'P12312'
and a9.account_number = 'P12313' and a10.account_number
= 'P12314' and a11.account_number = 'P12315' and
a12.account_number = 'P12316'
and a13.account_number = 'P12317' and a14.account_number
= 'P12318' and a15.account_number = 'P12319' and
a16.account_number = 'P12320'
and a17.account_number = 'P12321' and a18.account_number
= 'P12322' and a19.account_number = 'P12323' and
a20.account_number = 'P12324'
and a21.account_number = 'P12325' and a22.account_number
= 'P12326' and a23.account_number = 'P12327' and
a24.account_number = 'P12328'
and a25.account_number = 'P12329' and a26.account_number
= 'P12330' and a27.account_number = 'P12331' and
a28.account_number = 'P12332'
and a29.account_number = 'P12333' and a30.account_number
= 'P12334' and a31.account_number = 'P12335' and
a32.account_number = 'P12336'
and a33.account_number = 'P12337' and a34.account_number
= 'P12338' and a35.account_number = 'P12339'

and a1.report_byperiod = a2.report_byperiod
and a1.report_byperiod = a3.report_byperiod
and a1.report_byperiod = a4.report_byperiod
and a1.report_byperiod = a5.report_byperiod
and a1.report_byperiod = a6.report_byperiod
and a1.report_byperiod = a7.report_byperiod
and a1.report_byperiod = a8.report_byperiod
and a1.report_byperiod = a9.report_byperiod
and a1.report_byperiod = a10.report_byperiod
and a1.report_byperiod = a11.report_byperiod
and a1.report_byperiod = a12.report_byperiod
and a1.report_byperiod = a13.report_byperiod
and a1.report_byperiod = a14.report_byperiod
and a1.report_byperiod = a15.report_byperiod
and a1.report_byperiod = a16.report_byperiod
and a1.report_byperiod = a17.report_byperiod
and a1.report_byperiod = a18.report_byperiod
and a1.report_byperiod = a19.report_byperiod
and a1.report_byperiod = a20.report_byperiod
and a1.report_byperiod = a21.report_byperiod
and a1.report_byperiod = a22.report_byperiod
and a1.report_byperiod = a23.report_byperiod
and a1.report_byperiod = a24.report_byperiod
and a1.report_byperiod = a25.report_byperiod
and a1.report_byperiod = a26.report_byperiod
and a1.report_byperiod = a27.report_byperiod
and a1.report_byperiod = a28.report_byperiod
and a1.report_byperiod = a29.report_byperiod
and a1.report_byperiod = a30.report_byperiod
and a1.report_byperiod = a31.report_byperiod
and a1.report_byperiod = a32.report_byperiod
and a1.report_byperiod = a33.report_byperiod
and a1.report_byperiod = a34.report_byperiod
and a1.report_byperiod = a35.report_byperiod


So far the query returns what I expect pretty fast
although this is made up data and table format. My
quesiton is what is the limit or recommended limit on the
number of self joins?

It is 256 tables per SELECT statement. Though this is a technical limit, in
reality, depending on the complexity of joins, magnitude of data, data types
of the columns of tables, indexes involved etc, you may see performance
slowdown or sluggishness with your queries with a large number of tables.
However this is not a rule & in many cases, you may be able to avoid
self-joins using derived table constructs or CASE expressions. Your example
seems to be a classic example of pivoting, you can find some examples here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574

--
- Anith
( Please reply to newsgroups only )