Rajarshi Das Rajarshi Das - 4 months ago 9
MySQL Question

Sql to generate all combination from 4 tables

I have 3 tables

Table1: Table2: Table3:
**Users** **Posts** **Articles**

Name| Title| Title|
Raj Post1 Article1
Sujay Post2 Article2
Bijay Post3 Article3
Jijay Post4 Article4


Now I want to do combine all possible comination of them and put it into another table

Like I want to put it in a table tmp_all_cobinations

Table: **tmp_all_combinations**

Name
Raj.Post1.Aticle1
Raj.Post1.Aticle2
Raj.Post1.Aticle3
Raj.Post1.Aticle4
Raj.Post2.Aticle1
...
Raj.Post3.Aticle1
....
Sujay.

....


in this way

Any help will be really helpful

Answer

Do a cross join between the three tables, which in MySQL can be achieved by doing an INNER JOIN with no ON restriction.

INSERT INTO tmp_all_combinations (Users, Posts, Articles)
SELECT t1.Users, t2.Posts, t3.Articles
FROM Table1 t1
INNER JOIN Table2 t2
INNER JOIN Table3 t3

If you are using Postgres, then you can replace INNER JOIN with CROSS JOIN to get the same result.

If the tmp_all_combinations does not already exist, you can create it, making sure the columns have the same types as the three source tables.