SQL comparing 2 tables - help requested 1

[teach314]

hello

I have 2 tables, tblSet1 and tblSet2, whose values are generated from code. Both tables have 150 rows (the pk runs from 1 to 150) and 32 columns (the last 31 columns are a Distinct sequence of integers in ascending order). The first table looks like...

Set1 e0 e1 e2 e3 e4 e5 .... e30
1 1 3 5 7 8 11 .... 119
2 1 4 6 7 8 16 .... 110
...
149 2 3 4 5 6 9 .... 105
150 1 4 5 7 9 11 .... 101

The 2nd table has the identical structure...

Set2 e0 e1 e2 e3 e4 e5 .... e30
1 1 2 7 8 9 18 .... 125
2 2 3 4 5 6 9 .... 105
...
149 1 4 6 7 8 16 .... 110
150 1 3 5 7 8 11 .... 119


I need find ALL MATCHES of the e0 to e30 values. For example...

Set1 Set2
1 150
2 149
... ...
149 2

If this helps, there are exactly 150 distinct sets of e values (e0 to e30). Each of these occurs exactly once in each table, but in different rows. The final output should show 150 values of Set1, and the MATCHING 150 values of Set2.

I can do this in VBA, but I'm looking for a more efficient SQL solution. Any help is much appreciated.

Thanks, Teach314

 

[dhookom]

Have you attempted to create a query with the two tables and join all 30 fields from each table. This should find the matches.

Duane
Hook'D on Access
MS Access MVP

 

[teach314]

Thanks dhookom - that's much cleaner and faster than what I was trying to do. Joining each of the 31 'e' fields did the trick, producing a match for each of the 150 PK's. Given the nature of the data in the 2 tables, I was interested to notice that joining ANY 7 of these 31 fields sufficed to produce the same result.

Thanks for your help
Teach314