MySQL cartesian product conditions

Question

Hi

I need alittle help with a mysql query. I have 3 tables

x1 x2 x3
1  1  1
2  2  2
2  2  2

and I have 2 joins

select distinct

x1.int1 as a,
x2.int1 as b,
x3.int1 as c

from 
x1
JOIN
x2
JOIN 
x3

but I would like to generate the cartesian product with the condition that the results should contain just the just the 3 numbers from x1 (1,2,2) in all orders and I don't know what condition to put in the query

it's a permutation simulation of three elements(1,2,2) result should be 1,2,2 2,1,2 2,2,1

Thanks

Answer 1

Is that you want ?

SELECT DISTINCT * FROM x1 A,x1 B,x1 C

Answer 2

There are a number of ways to get the result you are after, given that the permutations are always made up of (1,2,2).

The simplest is to create a table containing the permutuations:

create table perm ( `int1` int, `int2` int, `int3` int );
insert into perm values (1,2,2), (2,1,2), (2,2,1);

Another is to take your existing joins, and restrict the answers to the set of valid permutations:

select distinct    
  x1.int1 as a,
  x2.int1 as b,
  x3.int1 as c
from x1
JOIN x2
JOIN x3
WHERE (a=1 and b=2 and c=2)
   OR (a=2 and b=1 and c=2)
   OR (a=2 and b=2 and c=1);

Another is to add the permutations table into the join:

select distinct
  x1.int1 as a,
  x2.int1 as b,
  x3.int1 as c
from x1
JOIN x2
JOIN x3
JOIN perm p on p.`int1` = a and p.`int2` = b and p.`int3` = c

Another approach would be to join against table x1 twice more, ensuring that every row in x1 appears in each result:

select distinct
  c1.int1 as a,
  x2.int1 as b,
  x3.int1 as c
from x1 as c1
JOIN x2
JOIN x3
JOIN x1 as c2 on c2.`int1` = b and c2.`int1` != c1.`int1` and c2.`int1` != c3.`int1` 
JOIN x1 as c3 on c3.`int1` = c and c3.`int1` != c1.`int1` and c3.`int1` != c2.`int1`

... but this will not work given that value 2 appears in x1 twice. Some unique per-row value would be needed to distinguish one row containing 2 from another.

The permutation table is easiest.

Answer 3

Second attempt - following clarification of question.

create table p ( bit int not null, v int not null );
insert into p values (1,1), (2,2), (4,2);

select distinct p1.v, p2.v, p3.v 
from p as p1 join p as p2 join p as p3 
where p1.bit + p2.bit + p3.bit = 7;

Column 'v' holds the values you want to permute ie. 1,2,2.

The important part is that column 'bit' must be assigned a unique value for each row, and the set of values must be such that the sum can only be arrived at if every row appears once and only once.

The simplest set of values to satisfy this is the sequence 2^0, 2^1 .. 2^31. You are limited to 32 rows for a 32-bit int. For a table with 3 rows, the sum is 1+2+4=7.

The result is:

+---+---+---+
| v | v | v |
+---+---+---+
| 2 | 2 | 1 |
| 2 | 1 | 2 |
| 1 | 2 | 2 |
+---+---+---+

If more rows are added, more joins have to be added to the query, and the sum of the bit column recalculated.