lundi 11 juillet 2016

How to INSERT all possible non-existing combinations in a many-to-many mapping table in mysql?

I have mapping table connecting two tables as

CREATE TABLE table1
(
t1_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
external_id tinyint(3) unsigned,
title varchar(255),
PRIMARY KEY(t1_id)
) ENGINE=InnoDB;

CREATE TABLE table2
(
t2_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
external_id tinyint(3) unsigned,
title varchar(255),
PRIMARY KEY(t2_id)
) ENGINE=InnoDB;

CREATE TABLE map_table
(
t1_id smallint(5) unsigned,
t2_id smallint(5) unsigned,
PRIMARY KEY(t1_id,t2_id)
) ENGINE=InnoDB;

How to place sub-query to INSERT all possible combinations of table1 and table2 (if not exists) into map_table where

table1.external_id = table2.external_id = x

Aucun commentaire:

Enregistrer un commentaire