mysql - Inner Join 在一个棘手的环境中，更新 Sql Fiddle，问题已隔离，正在寻找解决方案？

Question

假设我在“reference_data”表中有以下记录，假设它们都属于类别 224

parent_id   |   prop_id  |  property  | value    |  child_id
-----------------------------------------------------------------
    100     |    529     |    size    |    57    |    10250
    100     |    597     |    color   |    114   |    10250
    100     |    597     |    color   |    112   |    10248
    100     |    529     |    size    |    55    |    10248
    200     |    529     |    size    |    57    |    10300
    200     |    597     |    color   |    112   |    10300
    300     |    529     |    size    |    57    |    10342
    300     |    597     |    color   |    114   |    10342
    300     |    529     |    size    |    55    |    10500
    300     |    597     |    color   |    112   |    10500
    300     |    529     |    size    |    57    |    10900
    300     |    597     |    color   |    112   |    10900


SELECT `e`.`id`, `e`.`name` , `price`.`price` 
        FROM `object_record` AS `e`
      INNER JOIN `object_categories` AS `cat`
        ON cat.id = e.id AND cat.category_id = '224' 
    INNER JOIN `object_price` AS `price`
        ON price.id = e.id 
          INNER JOIN `reference_data` AS `color`
            ON color.parent_id = e.id
              AND color.prop_id = '597'
              AND color.value IN('112')
      INNER JOIN `reference_data` AS `size`
        ON size.parent_id = e.id
          AND size.prop_id = '529'
          AND size.value IN('57')

我的目标是仅当 prop_id 满足唯一 child_id 的值限制时才返回记录，现在发生的情况是我得到了parent_id = 200 的记录而不是 #300 返回，因为尺寸连接的第一条记录有一条颜色记录!= 112。有人有解决方案吗？

期望的结果:

`e`.`id`         |  `e`.`name`       |  `price`       
     200         |    object two     |     40     
     300         |    object three   |     50

下面链接的Sqlfiddle，它返回#200...在结果集中，当我向最后一个连接语句添加一个简单的 AND color.child_id = size.child_id 时，理论上它应该可以工作，但是当数据乱序时，它不会返回一些所需的记录。原因是数据库中 #300 的第一条记录的大小 = 57 的 child_id 的颜色 = 114，但是 #300 的记录有一个大小为 57 并且匹配的 child_id 的颜色为 112...但是它似乎数据的顺序导致了问题。

reference_data 表基本上是 EAV 索引表，其架构如下所示(减去我为便于阅读此问题而添加的“property”字符串字段):

SQL Fiddle 将返回 #200 以及以上信息....对于 SQL Fiddle sqlfiddle

CREATE TABLE `reference_data` (
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
  `prop_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
  `property` varchar(55) NOT NULL COMMENT 'string prop name for reference',
  `value` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Value',
  `child_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Child Entity Or Self ID',
  PRIMARY KEY (`parent_id`,`prop_id`, `value`,`child_id`),
  KEY `IDX_EAV_IDX_ID` (`parent_id`),
  KEY `IDX_EAV_IDX_PROP_ID` (`prop_id`),
  KEY `IDX_EAV_IDX_VALUE` (`value`),
  KEY `IDX_IDX_CHILD_ID` (`child_id`),
  KEY `IDX_INDEX_EAV_IDX_ID` (`parent_id`),
  KEY `IDX_INDEX_EAV_IDX_PROP_ID` (`prop_id`),
  KEY `IDX_INDEX_EAV_IDX_VALUE` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='EAV Indexer Index Table';

INSERT INTO `reference_data` (`parent_id`, `prop_id`, `property`, `value`, `child_id`)
VALUES
    (100, 529, 'size', 57, 10250),
    (100, 597, 'color', 114, 10250),
    (100, 529, 'size', 55, 10248),
    (100, 597, 'color', 112, 10248),
    (200, 529, 'size', 57, 10300),
    (200, 597, 'color', 112, 10300),
    (300, 529, 'size' , 55, 10500),
    (300, 529, 'size' , 57, 10342),
    (300, 597, 'color' , 114, 10342),
    (300, 597, 'color' , 112, 10500),
    (300, 597, 'color', 112, 10900);


CREATE TABLE `object_record` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'object id',
  `name` varchar(255) NOT NULL COMMENT 'object string name',
  PRIMARY KEY(`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='OBJECT RECORD TABLE';

INSERT INTO `object_record` ( `id`, `name` )
VALUES
    (100, 'object record one'),
    (200, 'object record two'),
    (300, 'object record three');

CREATE TABLE `object_categories` (
  `id` int(10) unsigned NOT NULL COMMENT 'object id id',
  `category_id` int(10) unsigned NOT NULL COMMENT 'category id'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='OBJECT CATEGORIES';

INSERT INTO `object_categories` ( `id`, `category_id` )
VALUES
    (100, 224),
    (200, 224),
    (300, 224);

CREATE TABLE `object_price` (
  `id` int(10) unsigned NOT NULL COMMENT 'object id id',
  `price` int(10) unsigned NOT NULL COMMENT 'price... assume whole #s for testing'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='OBJECT PRICE';

INSERT INTO `object_price` ( `id`, `price` )
VALUES
    (100, 30),
    (200, 40),
    (300, 50);

SQL Fiddle

附:添加 AND 后未返回的问题记录的图像:

Answer 1

仅添加一个附加连接条件:

and color.child_id = size.child_id

整个查询:

SELECT * FROM `object_record` AS `e`
INNER JOIN `data` AS `color`
    ON color.parent_id = e.entity_id AND color.prop_id = '597' AND color.value IN ('112')
INNER JOIN `data` AS `size`
    ON size.parent_id = e.entity_id AND size.prop_id = '529' AND size.value IN ('57')
    AND color.child_id = size.child_id