I have three tables:
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.
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
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 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)
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 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
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
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
table2). The additional columns
res1_2…res21_7 are type
Here’s what it looks like:
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
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.
table1 will be compared to
pair2s will only be compared with
pair3s only with
pair3s etc. So, no cross pair comparisons are made (eg
pair1 will never be compared with
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
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
Here we can see that
table1 matches both fields 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
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
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
table2. It will look like something like this when complete:
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
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
table3s, 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
JOINinstead of a
- Would there be a way to expand this to efficiently include the logic of the additional possible scenarios of sharing between the fields in
table2so that I can see or store in a new table all 50-800k results from the
table1to single row in
- 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.