MySQL LEFT JOIN do not return all results

I hav开发者_运维知识库e a query that JOINs some tables to get a list of products including prices, images, country etc.

"product_images" table can have zero or more images but only the default image should be returned. My problem is that in any case the query should return a result for the product even if there is no image in the "product_images" table for that particular product.

The first query example will return a row for each product but just return a random image:

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`

Below query will return the default image. But if there is no image in "product_images" the row for that product will not be retrieved. The only difference here is this part: "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`

 `pi`.`preset` = 1

in WHERE defeats your goal (your LEFT JOIN behaves like INNER ) . Move it to ON:

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

You may prefer to use a subquery:

LEFT JOIN (SELECT * FROM product_images WHERE preset = 1) AS pi ON ...

Otherwise, the WHERE clause is applied to the entire query, and if there's no image, WHERE pi.preset=1 will reduce your search set to zero rows.