将表多次连接到另一个可能具有或不具有这些值的表

Question

我有三个表：

| items
--------
id
quantity
size_attribute_value_id
color_attribute_value_id
type_attribute_value_id

| attribute_values (instance of an attribute, like red, xsmall)
--------
id
attribute_id
name

| attributes (attribute category, like color, size)
id
name 

三个属性（大小、颜色、类型）的任意组合都可以生成有效的项目。因此，我希望能够查询这三个属性的所有组合，同时还包括项目表中现有的项目。我的想法是我想要一个包含所有现有项目和所有可能的有效未来项目的结果集。例如，即使 items 表完全空白，我仍然应该得到数量为 0 的不存在但有效的项目的结果。

有效的 attribute_ids 为 1、2、4。

我尝试了以下右连接作为开始：

SELECT i.*, av1.*, av2.*, av3.*
FROM items i 
RIGHT OUTER JOIN attribute_values av1 ON av1.attribute_id = 2 AND av1.id = i.size_attribute_value_id
RIGHT OUTER JOIN attribute_values av2 ON av2.attribute_id = 4 AND av2.id = i.color_attribute_value_id
RIGHT OUTER JOIN attribute_values av3 ON av3.attribute_id = 1 AND av3.id = i.type_attribute_value_id;

但它只返回大约 200 行，而实际应该超过 1000 行。

任何帮助，即使只是正确方向的一点，我们都会表示赞赏。

Answer 1

SELECT a1.name as `type`, a2.name as `size`, a4.name as `color`, SUM(COALESCE( i.quantity , 0 ) ) as quantity
FROM (attribute_values a1, attribute_values a2, attribute_values a4)
LEFT JOIN items i
ON i.type_attribute_value_id = a1.id
AND i.size_attribute_value_id = a2.id
AND i.color_attribute_value_id = a4.id
WHERE a1.attribute_id = 1 AND a2.attribute_id = 2 AND a4.attribute_id = 4
GROUP BY a1.name, a2.name, a4.name

这假设您的项目表中除了类型、尺寸和颜色之外还有很多其他属性，并且可能有更多列，如果没有，最好将属性放在单独的表中，例如类型、颜色和尺寸，当数据库变大时，这将更容易查询，并通过适当的索引为您带来显着的性能改进。

Answer 2

我认为这应该可以做到 -

SELECT *
FROM (SELECT * FROM attribute_values WHERE attribute_id = 4) AS `color`
INNER JOIN (SELECT * FROM attribute_values WHERE attribute_id = 2) AS `size`
INNER JOIN (SELECT * FROM attribute_values WHERE attribute_id = 1) AS `type`
LEFT JOIN items
    ON `color`.`id` = `items`.`color_attribute_value_id`
    AND `size`.`id` = `items`.`size_attribute_value_id`
    AND `type`.`id` = `items`.`type_attribute_value_id`

我已经重写了这个查询以避免派生表。它应该表现得更好。

SELECT color.name, size.name, type.name, IFNULL(items.quantity, 0) AS quantity
FROM attribute_values AS `color`
INNER JOIN attribute_values AS `size`
    ON size.attribute_id = 2
INNER JOIN attribute_values AS `type`
    ON type.attribute_id = 1
LEFT JOIN items
    ON `color`.`id` = `items`.`color_attribute_value_id`
    AND `size`.`id` = `items`.`size_attribute_value_id`
    AND `type`.`id` = `items`.`type_attribute_value_id`
WHERE color.attribute_id = 4;