>
> But the result is omitting the records which has no Images under it.
>
Try this:
$select->from('products', array('id','product_name'));
$select->join('category','products.category_id = category.id', array());
$select->joinLeft('product_images',"products.id = product_images.product_id
AND product_images.main_pic = 'Y'", 'image');
$select->where('category.name = ?', 'categoryName');
The problem with your query was that you put the condition for main_pic =
'Y' in the WHERE clause. When the product has no images, main_pic IS NULL.
This means the comparison to 'Y' is false (or more specifically, unknown),
and the whole row is excluded from the result set.
When you need additional conditions on a table on the outer side of an outer
join, you need to put those conditions in the ON clause of that outer join.
This is standard SQL behavior and MySQL supports it.
Regards,
Bill Karwin
--
View this message in context: http://www.nabble.com/Need-help-in-Join-Query-tp19069913p19069997.html
Sent from the Zend DB mailing list archive at Nabble.com.
没有评论:
发表评论