Using INNER JOIN on multiple table columns for sql query in VB

[awyeah]

Hello!
I am trying to find common elements between 24 tables in access using VB 6.0. Currently I have developed the code for finding common elements between two columns in two tables only, you can see my pasted code.

The elements in the column "CustomerNo" have integer values, and the sql string returns the common elements found from the CustomerNo columns of two tables. This is the code:

Dim sql(0 To 11) As String
Dim month(0 To 11) As String
Dim lngPosition As Long

Dim mtype As Variant
mtype = Array("Month1", "Month2", "Month3", "Month4", "Month5", "Month6", "Month7", "Month8", "Month9", "Month10", "Month11", "Month12", "Month13", "Month14", "Month15", "Month16", "Month17", "Month18", "Month19", "Month20", "Month21", "Month22", "Month23", "Month24")
Dim mno As Variant
mno = Array("Month_1_and_2", "Month_3_and_4", "Month_5_and_6", "Month_7_and_8", "Month_9_and_10", "Month_11_and_12", "Month_13_and_14", "Month_15_and_16", "Month_17_and_18", "Month_19_and_20", "Month_21_and_22", "Month_23_and_24")

'INNER JOIN 24 records to obtain 12 records
For lngPosition = LBound(month) To UBound(month)

sql(lngPosition) = "SELECT DISTINCT " & mtype(lngPosition) & ".CustomerNo " _
& "FROM " & mtype(lngPosition) & " INNER JOIN " & mtype(lngPosition + 1) & "" _
& " ON " & mtype(lngPosition) & ".CustomerNo = " & mtype(lngPosition + 1) & ".CustomerNo"
Set rs = cn.Execute(sql(lngPosition))
.........................
.....................
.................

Currently I am using this string, to find common elements amongst two table columns "CustomerNo", and the result from this is used with the third table column and so on for all 24 columns (uses a looping mechanism) to find the common values in all 24 table columns. The code is tidious and very slow indeed and not very effective.

If I want to find the common for the CustomerNo in between 24 tables? Is it possible to use this sql string or looping in my case is better? The 24 tables have the CustomerNo column, however the number of rows are different in each table. My only concern is using 24 Tables with INNER JOIN separately, would be very complex.. Any ideas on how to solve this?

regards,
JD

[MaximilianMayrhofer]

You could try something like:

Dim mtype As String() = {"Month1", "Month2", "Month3", "Month4"}
Dim Month As String() = {"0", "1", "2", "3"}
Dim _SQL As String = "SELECT DISTINCT CustomerNo " & _
"FROM " & mtype(LBound(Month))
 
For lngPosition = LBound(Month) + 1 To UBound(Month)
    _SQL &= " INNER JOIN " & mtype(lngPosition) & " ON " & mtype(lngPosition) & ".CustomerNo = " & mtype(lngPosition - 1) & ".CustomerNo"
Next

That would give you:

SELECT DISTINCT CustomerNo FROM Month1 
INNER JOIN Month2 ON Month2.CustomerNo = Month1.CustomerNo 
INNER JOIN Month3 ON Month3.CustomerNo = Month2.CustomerNo 
INNER JOIN Month4 ON Month4.CustomerNo = Month3.CustomerNo

Why do you have two arrays though? Is it just to match the indices? Because you could simply do this:

Dim mtype As String() = {"Month1", "Month2", "Month3", "Month4"}
Dim _SQL As String = "SELECT DISTINCT CustomerNo " & _
"FROM " & mtype(0)
 
For X = 1 To mtype.Length - 1
    _SQL &= " INNER JOIN " & mtype(X) & " ON " & mtype(X) & ".CustomerNo = " & mtype(X - 1) & ".CustomerNo"
Next

[anhn]

That is a bad designed database. All months tables should be combined in one with an extra field [Month] added if required.

However, try this (I only try on 6 tables):
Noted: The parentheses in SQL are critical.

    Dim SQL As String
    Dim m As Integer
    
    SQL = "Month1"
    For m = 2 To 24
        SQL = "(" & SQL & " INNER JOIN Month" & m & " ON Month1.CustomerNo = Month" & m & ".CustomerNo)"
    Next
    '-- create Common Table
    SQL = "SELECT DISTINCT Month1.CustomerNo INTO Common FROM " & SQL
    DoCmd.RunSQL SQL
    '-- or select records only:
    'SQL = "SELECT Month1.CustomerNo FROM " & SQL
    '... ...

[awyeah]

Yes the database design I will work on it currently, after I do the preprocessing first. After preprocessing I will make the data to be represented into a more readable and suitable format.

Thanks for the reply anhn, your code works Assumingly, I don't have a very high end PC currently for simulation, my pc hangs up for the 24 loop.. since my data has 50,000+ customers on a monthly basis for two years.. hence I have divided it into three loops. The logic would be the same assumingly and give the same result in my opinion.

sql5 = "Month1"
 For m = 2 To 15
  sql5 = "(" & sql5 & " INNER JOIN Month" & m & " ON Month1.CustomerNo = Month" & m & ".CustomerNo)"
 Next
sql5 = "SELECT DISTINCT Month1.CustomerNo FROM " & sql5

sql6 = "Month13"
 For m = 14 To 24
  sql6 = "(" & sql6 & " INNER JOIN Month" & m & " ON Month13.CustomerNo = Month" & m & ".CustomerNo)"
 Next
sql6 = "SELECT DISTINCT Month13.CustomerNo FROM " & sql6

Then I INNER JOIN the result produced from these two sql queries, to get my final answer. Thanks a lot for the help guys, you made my day easier You truly are gurus.

regards,
JD