Rajarshi Das Rajarshi Das - 1 year ago 61
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**



in this way

Any help will be really helpful

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download