I have two table
Table1

NO | CITY  
1  | city1 
2  | city2

Table2

NO | PRODUCT  | CODE
1  | product1 | 1
2  | product2 | 2
3  | product3 | 1

and I want to combine table1 and table2 where table2 code is 1 and insert it into table3 like this
Table3

NO | CITY  | PRODUCT
1  | city1 | product1
2  | city1 | product3
3  | city2 | product1
4  | city2 | product3

Thank you for any help.

CodePudding user response：

If your NO is the AUTO_INCREMENT column, we can try to use INSERT INTO ... SELECT with CROSS JOIN to do that.

INSERT INTO Table3 (CITY  ,PRODUCT)
SELECT t1.CITY  ,t2.PRODUCT
FROM Table1 t1
CROSS JOIN Table2 t2
WHERE t2.CODE = 1