How to make valid combinations of many separate tables with subcombinations

Question

I have a challenge with some sql that I don't know how to get my head around. That's why I want yours input to help me guide in the right direction to find a solution.

The goal of the procedure that I would like to create, is to fill the following table with valid combinations of values. The columns represent six positions in a string. Like a sentence with six words, where only a certain pattern is allowed.

+------+------+------+------+------+------+
| Pos1 | Pos2 | Pos3 | Pos4 | Pos5 | Pos6 |
+------+------+------+------+------+------+
| AB   | CD   | FS   | KE   | HA   | KA   |
+------+------+------+------+------+------+
| ..   | ..   | ..   | ..   | ..   | ..   |
+------+------+------+------+------+------+

A combination (pattern) of the six positions is valid when all positions of the combination are validated against many other tables. In total there are 30 tables containing valid combinations of two of the positions. There are two tables per combination of positions. For example: one containing valid combinations Pos1 To Pos2 (Left to Right) And one containing valid combinations Pos2 To Pos1 (Right to Left). Table examples:

TableA

+------+------+
| Pos1 | Pos2 |
+------+------+
| AB   | CD   |
+------+------+
| AB   | EF   |
+------+------+
| AA   | GG   |
+------+------+

TableB

+------+------+
| Pos1 | Pos2 |
+------+------+
| AB   | YT   |
+------+------+
| AA   | YT   |
+------+------+
| AB   | CD   |
+------+------+

TableC

+------+------+
| Pos2 | Pos3 |
+------+------+
| CD   | FF   |
+------+------+
| CD   | UE   |
+------+------+
| EF   | KH   |
+------+------+

TableD

+------+------+
| Pos2 | Pos3 |
+------+------+
| CD   | FE   |
+------+------+
| CD   | UE   |
+------+------+
| EF   | KY   |
+------+------+

TableE

+------+------+
| Pos1 | Pos6 |
+------+------+
| AB   | ZZ   |
+------+------+
| AB   | ZF   |
+------+------+
| AA   | ZT   |
+------+------+

On so on....

I know that I can start using INTERSECT statements to intersect all combinations between two tables with the same positions, for example

INSERT INTO TempTable12
( SELECT *
  FROM TableA
  INTERSECT
  SELECT *
  FROM TableB ) Valid12;

INSERT INTO TempTable23
( SELECT *
  FROM TableC
  INTERSECT
  SELECT *
  FROM TableD ) Valid23;

And so on...

..but how can I do that when I want to continue with more than two positions? In other words to combine different positions in the query. Should I use temp tables like in to example above? Are there better ways?

I hope this is not too cryptic for you to understand and still help me get started. Thanks in advance!

score 2 · Answer 1 · answered Jan 01 '20 at 11:11

I'll start by creating test data that will get your final result. First, the CREATE TABLE statements; then, one insert into each table.

create table t12 (pos1 varchar2(2), pos2 varchar2(2));
create table t21 (pos1 varchar2(2), pos2 varchar2(2));
create table t13 (pos1 varchar2(2), pos3 varchar2(2));
create table t31 (pos1 varchar2(2), pos3 varchar2(2));
create table t14 (pos1 varchar2(2), pos4 varchar2(2));
create table t41 (pos1 varchar2(2), pos4 varchar2(2));
create table t15 (pos1 varchar2(2), pos5 varchar2(2));
create table t51 (pos1 varchar2(2), pos5 varchar2(2));
create table t16 (pos1 varchar2(2), pos6 varchar2(2));
create table t61 (pos1 varchar2(2), pos6 varchar2(2));
create table t23 (pos2 varchar2(2), pos3 varchar2(2));
create table t32 (pos2 varchar2(2), pos3 varchar2(2));
create table t24 (pos2 varchar2(2), pos4 varchar2(2));
create table t42 (pos2 varchar2(2), pos4 varchar2(2));
create table t25 (pos2 varchar2(2), pos5 varchar2(2));
create table t52 (pos2 varchar2(2), pos5 varchar2(2));
create table t26 (pos2 varchar2(2), pos6 varchar2(2));
create table t62 (pos2 varchar2(2), pos6 varchar2(2));
create table t34 (pos3 varchar2(2), pos4 varchar2(2));
create table t43 (pos3 varchar2(2), pos4 varchar2(2));
create table t35 (pos3 varchar2(2), pos5 varchar2(2));
create table t53 (pos3 varchar2(2), pos5 varchar2(2));
create table t36 (pos3 varchar2(2), pos6 varchar2(2));
create table t63 (pos3 varchar2(2), pos6 varchar2(2));
create table t45 (pos4 varchar2(2), pos5 varchar2(2));
create table t54 (pos4 varchar2(2), pos5 varchar2(2));
create table t46 (pos4 varchar2(2), pos6 varchar2(2));
create table t64 (pos4 varchar2(2), pos6 varchar2(2));
create table t56 (pos5 varchar2(2), pos6 varchar2(2));
create table t65 (pos5 varchar2(2), pos6 varchar2(2));

insert into t12 values('AB', 'CD');
insert into t21 values('AB', 'CD');
insert into t13 values('AB', 'FS');
insert into t31 values('AB', 'FS');
insert into t14 values('AB', 'KE');
insert into t41 values('AB', 'KE');
insert into t15 values('AB', 'HA');
insert into t51 values('AB', 'HA');
insert into t16 values('AB', 'KA');
insert into t61 values('AB', 'KA');
insert into t23 values('CD', 'FS');
insert into t32 values('CD', 'FS');
insert into t24 values('CD', 'KE');
insert into t42 values('CD', 'KE');
insert into t25 values('CD', 'HA');
insert into t52 values('CD', 'HA');
insert into t26 values('CD', 'KA');
insert into t62 values('CD', 'KA');
insert into t34 values('FS', 'KE');
insert into t43 values('FS', 'KE');
insert into t35 values('FS', 'HA');
insert into t53 values('FS', 'HA');
insert into t36 values('FS', 'KA');
insert into t63 values('FS', 'KA');
insert into t45 values('KE', 'HA');
insert into t54 values('KE', 'HA');
insert into t46 values('KE', 'KA');
insert into t64 values('KE', 'KA');
insert into t56 values('HA', 'KA');
insert into t65 values('HA', 'KA');
commit;

Now for the solution. By starting with INTERSECT, you are on the right track. Using a WITH clause, you can list all the intersection subqueries and reduce 30 tables to 15 subqueries. After that, you need to start joining. I use the USING clause in the joins because it does an equality join and it coalesces the join columns.

with q12 as (select * from t12 intersect select * from t21)
, q13 as (select * from t13 intersect select * from t31)
, q14 as (select * from t14 intersect select * from t41)
, q15 as (select * from t15 intersect select * from t51)
, q16 as (select * from t16 intersect select * from t61)
, q23 as (select * from t23 intersect select * from t32)
, q24 as (select * from t24 intersect select * from t42)
, q25 as (select * from t25 intersect select * from t52)
, q26 as (select * from t26 intersect select * from t62)
, q34 as (select * from t34 intersect select * from t43)
, q35 as (select * from t35 intersect select * from t53)
, q36 as (select * from t36 intersect select * from t63)
, q45 as (select * from t45 intersect select * from t54)
, q46 as (select * from t46 intersect select * from t64)
, q56 as (select * from t56 intersect select * from t65)
select pos1, pos2, pos3, pos4, pos5, pos6
from q12
join q13 using(pos1)
join q14 using(pos1)
join q15 using(pos1)
join q16 using(pos1)
join q23 using(pos2, pos3)
join q24 using(pos2, pos4)
join q25 using(pos2, pos5)
join q26 using(pos2, pos6)
join q34 using(pos3, pos4)
join q35 using(pos3, pos5)
join q36 using(pos3, pos6)
join q45 using(pos4, pos5)
join q46 using(pos4, pos6)
join q56 using(pos5, pos6);

POS1 POS2 POS3 POS4 POS5 POS6
---- ---- ---- ---- ---- ----
AB   CD   FS   KE   HA   KA

score 0 · Answer 2 · answered Dec 31 '19 at 17:37

I think you can do this with a massive join. I don't fully understand why you have 30 tables instead of 15, but the idea is:

select tab12.pos1, tab12.pos2, tab13.pos3,
       tab14.pos4, tab15.pos5, tab16.pos6
from tab12 join
     tab13
     on tab12.pos1 = tab13.pos1 join
     tab14
     on tab12.pos1 = tab14.pos1 join
     tab15
     on tab12.pos1 = tab15.pos1 join
     tab16
     on tab12.pos1 = tab16.pos1

Then filter these for the other conditions, either using exists, in, or further joins:

select tab12.pos1, tab12.pos2, tab13.pos3,
       tab14.pos4, tab15.pos5, tab16.pos6
from tab12 join
     tab13
     on tab12.pos1 = tab13.pos1 join
     tab14
     on tab12.pos1 = tab14.pos1 join
     tab15
     on tab12.pos1 = tab15.pos1 join
     tab16
     on tab12.pos1 = tab16.pos1 join
     tab23
     on tab23.pos2 = tab12.pos2 and
        tab23.pos3 = tab13.pos3 join
     tab23
     on tab24.pos2 = tab12.pos2 and
        tab24.pos3 = tab14.pos4 join
     . . .

How to make valid combinations of many separate tables with subcombinations

2 Answers2