mysql - How to show zero when no result is returned in grouped joined query -

i trying compact down number of queries on (php) mysql database , have following problem:

for last 10 posted comments, show average comment rating , whether there image associated comment

the mysql is:

select  obj_images.fk_obj_id has_images, avg(obj_rating) rating /*other joined tables obj_comments omitted clarity ...*/ obj_comments inner join obj_images on obj_comments.fk_obj_id=obj_images.fk_obj_id group obj_comments.fk_obj_id order comment_id desc limit 10 

even when there no image associated comment, has_images still returns value, ideally want return 0 when there no images associated comment , 1 when there images associated it

what missing?

thanks tips

use outer join , ifnull() instead of inner join.

select    avg(c.obj_rating) avg_rating   ifnull(i.fk_obj_id, 0) has_images,   /* ... */    obj_comments c   left join obj_images on c.fk_obj_id = i.fk_obj_id group    c.fk_obj_id order    c.comment_id desc limit 10 

also think declaring table aliases idea.