CODEWITHSUNDEEP
mysqlsql
john BB
john BB

Reputation: 79

mysql insert 2 tables into another table use JOIN

I have 2 tables:

table1:

+-----+-----+-------+
| ID  | num | title |
+-----+-----+-------+
| 101 |  0  | pro   |
+-----+-----+-------+
| 102 |  0  | xtr   |
+-----+-----+-------+
| 103 |  0  | low   |
+-----+-----+-------+
| 104 |  0  | hi    |
+-----+-----+-------+

and table2:

+-----+--------+
| Nn  | title  |
+-----+--------+
|  1  | ttl1   |
+-----+--------+
|  2  | ttl2   |
+-----+--------+
|  3  | ttl3   |
+-----+--------+

And I need mysql request to fill table 3 like:

+-----+-----+
| ID  | Nn  |
+-----+-----+
| 101 |  1  |
+-----+-----+
| 101 |  2  |
+-----+-----+
| 101 |  3  |
+-----+-----+
| 102 |  1  |
+-----+-----+
| 102 |  2  |
+-----+-----+
| 102 |  3  |
+-----+-----+
| 103 |  1  |
+-----+-----+
| 103 |  2  |
+-----+-----+
| 103 |  3  |
...

i.e. for each ID from table1 I need all Nn from table2.

I wrote the following sql request, which works well, but I need the sql request to use a JOIN statement, how can I do that?

INSERT INTO `table3` ( `ID`, `Nn` ) 
     SELECT `t1`.`ID`, `t2`.`Nn` 
     FROM `table1` as `t1`, `table2` as `t2`;

Upvotes: 1

Views: 98

Answers (2)

Francisco Romero
Francisco Romero

Reputation: 13189

Try with this:

SELECT t1.ID, t2.Nn
FROM table1 t1, table2 t2
INNER JOIN table3;

Upvotes: 0

TobyLL
TobyLL

Reputation: 2296

Use a cross join:

INSERT INTO `table3` ( `ID`, `Nn` ) 
 SELECT `t1`.`ID`, `t2`.`Nn` 
 FROM `table1` as `t1` CROSS JOIN `table2` as `t2`;

Upvotes: 2

Related Questions