Fill junction table by matching values in linked tables

Question

I have a database with several articles and corresponding images (laying in different folders)

There are 3 folders with Images of the products

In one folder only technical images, in the other marketing images, in the third are high quality images for printing purposes

To retrieve product information or images faster we use 'slugs', the Article Number is stripped from any character besides digits

i did the same on the image table, but hence there are a lot of images linked to one product (and sometimes one image is linked to different products) i would like to link them in a junction table

is it possible to fill the junction table with the IDs based on comparing the value in tblArticles.articleSlug and tblArticlepictures.articlepictureSlug

I found this resource, but it is based on a 1 to many relationship

How do I insert into a table from another table by matching on values?

CREATE TABLE `tblArticles` (
  `articleID` int(11) NOT NULL AUTO_INCREMENT,
  `articleSlug` varchar(16) DEFAULT NULL,
  `articleComment` varchar(1500) DEFAULT '',
  PRIMARY KEY (`articleID`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tblArticlepictures` (
  `articlepictureID` int(11) NOT NULL AUTO_INCREMENT,
  `articlepictureYear` int(4) DEFAULT NULL,
  `articlepicturePath` varchar(255) NOT NULL,
  `articlepictureSlug` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`articlepictureID`),
  UNIQUE KEY `articlepicturePath_UNIQUE` (`articlepicturePath`),
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tblRefArticleArticlepicture` (
  `refArticleArticlepictureID` int(11) NOT NULL AUTO_INCREMENT,
  `articleIDRef` int(11) NOT NULL,
  `articlepictureIDRef` int(11) NOT NULL,
  PRIMARY KEY (`refArticleArticlepictureID`),
  KEY `fk_tblRefArticleArticlepicture_tblArticles1_idx` (`articleIDRef`),
  KEY `fk_tblRefArticleArticlepicture_tblArticlepictures1_idx` (`articlepictureIDRef`),
  CONSTRAINT `fk_tblRefArticleArticlepicture_tblArticlepictures1` 
  FOREIGN KEY (`articlepictureIDRef`) REFERENCES `tblArticlepictures` (`articlepictureID`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tblRefArticleArticlepicture_tblArticles1` 
  FOREIGN KEY (`articleIDRef`) REFERENCES `tblArticles` (`articleID`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

+-------------------+-------------------+-------------------+-----------------------+
| Picture           | Folder            | Article           | Slug (in both tables) |
+-------------------+-------------------+-------------------+-----------------------+
| APS-4216_wr.jpg   | W                 | APS-4216T         | 4216                  |
+-------------------+-------------------+-------------------+-----------------------+
| APS-9669 .jpg     | W                 | APS-9669          | 9669                  |
+-------------------+-------------------+-------------------+-----------------------+
| APS-2719NC.jpg    | W                 | APS-2719          | 2719                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-3277T.jpg     | W                 | EAP-3277          | 3277                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-8717T_1.jpg   | W                 | EAP-8717Z         | 8717                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-4530T.jpg     | W                 | EAP-4530A         | 4530                  |
+-------------------+-------------------+-------------------+-----------------------+
| ----------------- | ----------------- | ----------------- | -----------------     |
+-------------------+-------------------+-------------------+-----------------------+
| APS-4216 t.jpg    | P                 | APS-4216T         | 4216                  |
+-------------------+-------------------+-------------------+-----------------------+
| APS-9669_wr.jpg   | P                 | APS-9669          | 9669                  |
+-------------------+-------------------+-------------------+-----------------------+
| APS-2719 .jpg     | P                 | APS-2719          | 2719                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-3277NC.jpg    | P                 | EAP-3277          | 3277                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-8717T.jpg     | P                 | EAP-8717Z         | 8717                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-4530T_1.jpg   | P                 | EAP-4530A         | 4530                  |
+-------------------+-------------------+-------------------+-----------------------+

score 0 · Answer 1 · answered Jun 30 '17 at 23:37

insert into tblRefArticleArticlepicture (articleIDRef, articlepictureIDRef)
select articleID as articleIDRef, articlepictureID as articlepictureIDRef
        from tblArticles a
        join tblArticlepictures p
        ON a.articleSlug = p.articlepictureSlug
        where a.articleSlug =p.articlepictureSlug

Fill junction table by matching values in linked tables

1 Answers1