Return to Question

So I want to choose say pair1_3 from table1234B

So I want to choose say pair1_4 from table1234B

This 3rd table will contain 4 rows. It has a GROUP 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).

This is closer to what I am looking for as it leaves the NAMEs with different sharing open to other values from table1234B. I intend the remaining logic once incorporated would return the other appropriate results.

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).

This is closer to what I am looking for as it leaves the NAMEs from table2 with different sharing open to other values from table1234B. I intend the remaining logic once incorporated would return the other appropriate results.

GROUP       res1_1  res1_2  res1_3  res1_4  res1_5  res1_6  res1_7  ….res21_7
Group1      12.30   1.000   0.2500  1.000   2.000   2.10    25.00   ….
…
Group4

Here is the query I’m starting with (before anyone says this should be a JOIN instead of a WHERE clause, that’s part of why I am here)

SELECT t1.NAME as NAME, t3.pair1_4 as RESULTPair1
FROM table1 t1, table1234B t3, table2 t2
WHERE (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)

NAME        RESULTPair1 
00001A      1.000       (res1_4 from table3)                
00004A      1.000       (res1_4 from table3)

Here I will have less than 800k rows because I'm only requesting anything that is an AB, AB set of pairs in my query. The remaining logic once incorporated would return the other results up to 800k.

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   ….

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

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 NAMEs with different sharing open to other values from table1234B. I intend the remaining logic once incorporated would return the other appropriate results.

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.