I used 1 table to do a cross join 4 times giving me 5 columns of data. I used the same column in all 4 cross joins to get every combination. The only problem is I get the same data it is in a different order. I am trying to only get records that are unique.
I can only have one iteration of each set of data, ie 12345 – 12346 – 12347, but not 54321 – 64321 – 74321 or any combo where all the same numbers are combined. I am stumped.
| Table 1 / column 1 |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| … |
| 100 |
Select t.col1, t1.col1, t2.col1, t3.col1, t4.col1
from table1 t
CROSS JOIN table1 t1
CROSS JOIN table1 t2
CROSS JOIN table1 t3
CROSS JOIN table1 t4
WHERE
t.col1 != t1.col1 and t.col1 != t2.col1 and t.col1 != t3.col1 and t.col1 != t4.col1
and t1.col1 != t2.col1 and t1.col1 != t3.col1 and t1.col1 != t4.col1
and t2.col1 != t3.col1 and t2.col1 != t4.col1
and t3.col1 != t4.col1
| col1 | col2 | col3 | col4 | col5 |
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 3 | 4 | 5 |
| 3 | 2 | 1 | 4 | 5 |
| 5 | 2 | 4 | 3 | 1 |
| 5 | 4 | 3 | 2 | 1 |
Only one of those works, the first one, because all the rest are just combinations of the first. Think of this with the original table having hundreds of rows.
Advertisement
Answer
Replace all =! with <:
Select t.col1, t1.col1, t2.col1, t3.col1, t4.col1
from table1 t
CROSS JOIN table1 t1
CROSS JOIN table1 t2
CROSS JOIN table1 t3
CROSS JOIN table1 t4
WHERE
t.col1 < t1.col1 and t.col1 < t2.col1 and t.col1 < t3.col1 and t.col1 < t4.col1
and t1.col1 < t2.col1 and t1.col1 < t3.col1 and t1.col1 < t4.col1
and t2.col1 < t3.col1 and t2.col1 < t4.col1
and t3.col1 < t4.col1
The problem with =! is while you prevent numbers x and y from being the same, you get two joins; one for the case where x < y and one for the case where x > y, leading to unwanted duplicate joins.