MySQL LEFT JOIN 不返回所有结果

Question

我有一个连接一些表的查询，以获取产品列表，包括价格、图片、国家/地区等。

“product_images”表可以有零个或多个图像，但只应返回默认图像。我的问题是，在任何情况下，查询都应该返回产品的结果，即使该特定产品的“product_images”表中没有图像也是如此。

第一个查询示例将为每个产品返回一行，但只返回一张随机图像:

SELECT `cp`.`category_id`, `p`.`id`, `p`.`master`, `p`.`status`, `p`.`sortorder`, `p`.`sku`, `p`.`stock`, `pd`.`name`, `pd`.`short_description`, `pd`.`description`, `pd`.`slug`, `pi`.`image`, `pi`.`path`
FROM `categories_products` AS `cp`
JOIN `products` AS `p` ON (`cp`.`product_id` = `p`.`id`)
JOIN `product_descriptions` AS `pd`
ON (`pd`.`product_id` = `p`.`id`)
LEFT JOIN `product_images` AS `pi`
ON (`pi`.`product_id` = `p`.`id`)
WHERE `cp`.`category_id` = 34
AND `pd`.`locale_id` = 1
AND `p`.`master` = '0'
AND `p`.`status` = '1'
AND `p`.`accessible` = '1'
AND `pd`.`status` = '1'
GROUP BY `p`.`id`
ORDER BY `p`.`sortorder`

下面的查询将返回默认图像。但是，如果“product_images”中没有图像，则不会检索该产品的行。这里唯一的区别是这部分:“AND pi.preset = 1”

SELECT `cp`.`category_id`, `p`.`id`, `p`.`master`, `p`.`status`, `p`.`sortorder`, `p`.`sku`, `p`.`stock`, `pd`.`name`, `pd`.`short_description`, `pd`.`description`, `pd`.`slug`, `pi`.`image`, `pi`.`path`
FROM `categories_products` AS `cp`
JOIN `products` AS `p` ON (`cp`.`product_id` = `p`.`id`)
JOIN `product_descriptions` AS `pd`
ON (`pd`.`product_id` = `p`.`id`)
LEFT JOIN `product_images` AS `pi`
ON (`pi`.`product_id` = `p`.`id`)
WHERE `cp`.`category_id` = 34
AND `pi`.`preset` = 1
AND `pd`.`locale_id` = 1
AND `p`.`master` = '0'
AND `p`.`status` = '1'
AND `p`.`accessible` = '1'
AND `pd`.`status` = '1'
GROUP BY `p`.`id`
ORDER BY `p`.`sortorder`

Answer 1

 `pi`.`preset` = 1

在 WHERE 中打败了你的目标(你的 LEFT JOIN 表现得像 INNER )。将其移至 ON:

LEFT JOIN `product_images` AS `pi`
ON (`pi`.`product_id` = `p`.`id` AND `pi`.`preset` = 1)