Nikos P Nikos P - 2 months ago 5
MySQL Question

How to insert combinations from 3 tables into a 4th table with 3 corresponding columns?

I have the following tables:

table1: table2: table3:

| id | | id | | id |
|------| |------| |------|
| 1 | | 1 | | 1 |
| 2 | | 2 | | 2 |


How can I execute a MySql Query which inserts into a 4th table (table4) all possible combinations of the above tables which has according columns (id1, id2, id3)?

Basically I want my table4 to look like this:

| id1 | id2 | id3 |
|-----|-----|-----|
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 1 |
| 1 | 2 | 2 |
| 2 | 1 | 1 |
| 2 | 1 | 2 |
| 2 | 2 | 1 |
| 2 | 2 | 2 |

Answer

You would use create table as and cross join:

create table table4 as
    select t1.id as id1, t2.id as id2, t3.id as id3
    from table1 t1 cross join
         table2 t2 cross join
         table3 t3;

Note that SQL tables represent unordered sets. So, if you want to see the results in the order you have specified, then use:

select t4.*
from table4 t4
order by id1, id2, id3;
Comments