CODEWITHSUNDEEP
mysqlsql
Tom
Tom

Reputation: 12998

MySQL query to pre-populate join table with all id combinations from two other tables

I have the following query which populates a join table with all 'theme' options for a given user.

INSERT INTO join_themes_users (join_theme_id, join_user_id);
SELECT theme_id, 1
FROM themes

What I now need to do is edit this query to populate the join table with all theme options for all users

Can this be done in a single query or will I need to create a for loop in PHP?

The above query produces results looking something like...

join_id     |     user_id      |      theme_id
----------------------------------------------------
1              1                  1
2              1                  2
3              1                  3
...

What I need is something like this...

join_id     |     user_id      |      theme_id
----------------------------------------------------
1              1                  1
2              1                  2
3              1                  3
...
14             2                  1
15             2                  2
16             2                  3
...
27             3                  1
28             3                  2
29             3                  3
...

Upvotes: 0

Views: 732

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

To get all combinations you cross join the tables:

INSERT INTO join_themes_users (join_theme_id, join_user_id)
SELECT themes.theme_id, users.user_id
FROM themes 
CROSS JOIN users

Upvotes: 2

Related Questions