I have three tables:
Table1
table1
can have anywhere from 50K to 800k unique rows. By this, I mean that the combination of all the values in each row are unique, although sometimes, some columns will match up. It’s POSSIBLE that an entire row will match except the NAME
column, but it’s very, very unlikely. The NAME column will always be unique.
The NAME
column is type varchar(20)
. The remainder of the columns for each record are all type varchar(6)
, where every two columns is a set pair and the table has up to 21 set pairs (ie 43 columns total). Here is an example table1
showing one set pair for 4 records (I used single characters here for ease but don’t forget they are type varchar(6)
):
table1
NAME pair1_1 pair1_2 ...up to pair21_1 - pair21_2
00001A A B
00002A A A
00003A B C
00004A A B
…up to 800k rows
Table2
table2
is set up exactly the same way except it contains completely different records of the same exact type (here there can be anywhere from 1 to 200 rows)
table2
NAME pair1_1 pair1_2 ...up to pair21_1 - pair21_2
1234B A B
5678B A A
9101B C C
1213B A B
…up to 200 rows
Table3
table3
is associated with a single row in table2
and can be characterized as every possible result of a comparison between that single row in table2
to any possible row that COULD BE in table1
. It’s probably best to call it by the NAME
in table2
pertaining to it (let’s use just the first and call it table1234B
because if there are 200 rows in table2, there are 200 different associated table3
s).
This 3rd table will contain 4 rows. It has a NAME
column which is a varchar(20)
and 21 sets of 7 columns (each associated with a different pair from table1
and table2
). The additional columns res1_1
, res1_2…res21_7
are type decimal(30,7)
.
Here’s what it looks like:
table1234B
NAME res1_1 res1_2 res1_3 res1_4 res1_5 res1_6 res1_7 ….res21_7
1234B 12.30 1.000 0.2500 1.000 2.000 2.10 25.00 ….
I want to use the combination of shared data in the pairs of columns from table1
and table2
(ie how they match up) as the condition to select data from table1234B
(I will show some of this below). Only coinciding pairs will be compared. pair1_1
and pair1_2
from table1
will be compared to pair1_1
and pair1_2
from table2
, the pair2
s will only be compared with pair2
s, pair3
s only with pair3
s etc. So, no cross pair comparisons are made (eg pair1
will never be compared with pair2
)
In this example below, the fields in pair1_1
from each table match (A), and the fields in pair1_2
from each table match (B) but the fields don’t match between the columns of each table.
NAME Pair1_1 Pair1_2
00001A A B (from table1)
1234B A B (from table2)
So I want to SELECT say pair1_4
from table1234B
and associate it with the comparison between records 00001A and 1234B.
If the tables were like this we can see that all 4 of the fields match.
NAME pair1_1 pair1_2
00001A A A (from table1)
1234B A A (from table2)
In this instance maybe I would want to choose pair1_1
from table1234B
Here we can see that pair1_1
in table1
matches both fields from table2
but pair1_2
from table1
does not match anything.
NAME pair1_1 pair1_2
00001A A B (from table1)
1234B A A (from table2)
So I want to choose say pair1_4
from table1234B
The above are only 3 of the 14 possible ways the data in the column pairs can be shared between the tables (there are multiple ways they share no data between tables also) but only 7 possible columns for each pair to select from table1234B
.
I want to select ALL of the values from table1234B
that meet the criteria set forth from all of the possible sharing between the columns in each set of 2 from table1
and table2
. It will look like something like this when complete:
1234BResult
NAME RESULTPair1 …up to Resultpair21
00001A 12.30 (res1_1 from table3)
00002B 1.000 (res1_2 from table3)
00003C 25.00 (res1_7 from table3)
00004A 1.000 (res1_4 from table3)
…up to 800K rows
Here is my edited query I’m starting with.
SELECT t1.NAME as NAME, t3.pair1_4 as RESULTPair1
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON (t1.pair1_1 = t2.pair1_1 AND t1.pair1_1 <> t1.pair1_2 AND t1.pair1_2 = t2.pair1_2 AND t2.pair1_1 <> t2.pair1_2)
LEFT OUTER JOIN table1234B t3
ON t2.NAME = t3.NAME
…and the result of that query
NAME RESULTPair1
00001A 1.000 (res1_4 from table3)
00002B NULL
00003A NULL
00004A 1.000 (res1_4 from table3)
This is closer to what I am looking for as it leaves the NAME
s from table2
with different sharing open to other values from table1234B
. I intend the remaining logic once incorporated would return the other appropriate results.
The trouble is that this only gives me the result of any situation that is AB, AB. I need to expand this to 21 pairs and potentially 200 table3
s, including all of the different possible results from table3
if applicable. I can handle all the sharing logic (ie pair1_1 = pairt2_2
or pair1_1 <> pair2_2
) it’s expanding it to the remaining situations and potentially more records from table2
that I am at a loss on. I need to get results for all 800k records in table1
for all 21 pairs.
If you’ve stayed with me this long and understand all that chicken scratch, my questions are:
- How would I edit my above query to work with a
JOIN
instead of aWHERE
? - Would there be a way to expand this to efficiently include the logic of the additional possible scenarios of sharing between the fields in
table1
andtable2
so that I can see or store in a new table all 50-800k results from thetable1
to single row intable2
comparison? - How the heck do I expand it to the additional 20 pairs?
EDIT After being asked about the connection with table3 I realized that the column needed an edit for this to work properly. Im still having trouble figuring out how to make the query work right. I have edited the query too. Im still having a little trouble expanding it to multiple columns. It was suggested to use INTERSECT + EXCEPT or NOT EXISTS. I couldn't get INTERSECT to return anything whereas the query with the edit to include the left joins does.
t1
andt3
being across join
, and the second comma aninner join
(because of the where clause). Looking into theINTERSECT
+EXCEPT
operators ornot exists
is probably going to be better performing, might be me, but table3, (that represents a huge amount of tables?) connection to all of this is still a tad unclear to me.