Weird SQL join produces different results than splitting it up

Question

I am importing table data from one CMS to another. Therefore, I have the model with "pn_" prefix, which is the old one. The tables of the new one carry "avsn_" in front.

There are game variants, gallery images and gallery categories. The tables look like this:

CREATE TABLE `avsn_gallery_categories2variants` (
  `categoryId` int(255) unsigned NOT NULL AUTO_INCREMENT, 
  `gameVariantId` int(255) NULL,  
  PRIMARY KEY (`categoryId`,`gameVariantId`)  
);

CREATE TABLE `avsn_games_variant` (
        `psId`  int(255) NOT NULL ,
        `variantId`  int(255) NOT NULL AUTO_INCREMENT , 
        `gameId`  int(255) NOT NULL ,
        `variantTitle`  varchar(1000) NOT NULL ,
        `consoleId`  int(255) NOT NULL ,    
        `specialGenreID`  int(255) NULL,
        `releaseDay`  int(2) NOT NULL ,
        `releaseMonth`  int(2) NOT NULL ,
        `releaseYear`  int(4) NOT NULL ,
        `isFreeware`  bit NOT NULL ,
        `isReleased`  bit NOT NULL ,
        `installationAdvices`  text NULL ,
        `gameAdvices`  text NULL ,
        `additionalInformation`  text NULL ,
        `cover`  varchar(1000) NOT NULL ,
        `publishingStatus`  int(1) NOT NULL ,
        `creatorId`  int(255) NOT NULL ,
        `lastModified`  date NOT NULL ,
        `creationDate`  date NOT NULL ,
        PRIMARY KEY (`variantId`)
    );

The old tables are these:

CREATE TABLE `pn_gallery_categories` (
  `gallid` int(3) NOT NULL AUTO_INCREMENT,
  `gallname` varchar(80) NOT NULL DEFAULT '',
  `gallimg` varchar(50) NOT NULL DEFAULT '',
  `server` int(11) NOT NULL DEFAULT '1',
  `galloc` longtext NOT NULL,
  `description` text NOT NULL,
  `parent` int(3) NOT NULL DEFAULT '-1',
  `visible` int(1) NOT NULL DEFAULT '0',
  `template` varchar(25) NOT NULL DEFAULT 'Default',
  `thumbwidth` int(2) unsigned NOT NULL DEFAULT '120',
  `numcol` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `total` int(10) unsigned NOT NULL DEFAULT '0',
  `lastadd` date DEFAULT NULL,
  PRIMARY KEY (`gallid`),
  KEY `gallid` (`gallid`)
) ENGINE=MyISAM AUTO_INCREMENT=2166 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;   

The table avsn_gallery_categories2variants is not imported properly. There is more than one combination of a specific category with a variant. This should not be possible due to the old model allowing just one combination. I think there is something wrong with the JOIN in the import:

INSERT INTO
    `avsn_gallery_categories2variants`
    (
        SELECT 
            variant.variantId AS `variantId`,
            gallid            AS `categoryId`
        FROM
            pn_gallery_categories
        JOIN
            avsn_games_variant variant ON gallimg = variant.cover
    );

What I did for verification:

1 Make sure that there really is just 1 category:

mysql> SELECT * FROM avsn_gallery_categories WHERE categoryId = 1897;
+------------+----------+---------------+---------------+--------+----------------+
| categoryId | parentId | title         | folder        | userId | isUserCategory |
+------------+----------+---------------+---------------+--------+----------------+
|       1897 |      630 | Gray Matter 2 | G/GrayMatter2 |     -1 |                |
+------------+----------+---------------+---------------+--------+----------------+
1 row in set (0.00 sec)

2 Make sure, that there is just one association by requesting it directly:

mysql> SELECT * FROM avsn_gallery_categories2variants WHERE categoryId = 1897;
+------------+---------------+
| categoryId | gameVariantId |
+------------+---------------+
| 1897 | 365 |
| 1897 | 542 |
+------------+---------------+
2 rows in set (0.01 sec)

Oops! 2 rows. Okay, now check the JOIN fields for both variants:

The category's cover:

mysql> SELECT gallimg FROM pn_gallery_categories WHERE gallid = 1897;
+-----------------+
| gallimg         |
+-----------------+
| graymatter2.png |
+-----------------+
1 row in set (0.00 sec)

ID 365:

mysql> SELECT cover FROM avsn_games_variant WHERE variantId = 365;
+-----------+
| cover     |
+-----------+
| radau.jpg |
+-----------+
1 row in set (0.00 sec)

ID 542:

mysql> SELECT cover FROM avsn_games_variant WHERE variantId = 542;
+-----------+
| cover     |
+-----------+
| tkkg5.jpg |
+-----------+
1 row in set (0.00 sec)

Errm. Yeah. None of them fits the category. What is going wrong? I am kind of desperate finding a solution. Mabye I wrote too much SQL and can't see issues anymore by having an import script of like 5,5k lines.

I appreciate any good answers!

Answer 1

I think it should be :

INSERT INTO
    `avsn_gallery_categories2variants`
    (
        SELECT 
            gallid            AS `categoryId`,
            variant.variantId AS `variantId`
        FROM
            pn_gallery_categories
        JOIN
            avsn_games_variant variant ON gallimg = variant.cover
    );

Or better yet :

INSERT INTO
    `avsn_gallery_categories2variants`
    (`categoryId`, `variantId`)
    (
        SELECT 
            gallid,
            variant.variantId
        FROM
            pn_gallery_categories
        JOIN
            avsn_games_variant variant ON gallimg = variant.cover
    );

I get from the official documentation that if you do not explicitly declare target columns, the order of the columns in the inner select should match the order of the columns in the target table. I don't think aliasing the columns in the select part has any effect.