mysql join statement

Question

Hello,

I have a category page in which i am displaying all images coming under a particular category...I also have a dropdown using which i can sort the images based on number of stories written for that image... To accomplish this i am using JOIN statement coz media and story are two different tables...

media table has mediaid,catid,userid,link

and

story table has storyid,mediaid,storyheading,storycontent

i am using JOIN statement in this way...

SELECT count(story.id) as cnt,story.mediaid as id,media.cat_id,media.link,media.userid 
FROM story RIGHT JOIN 
media ON story.mediaid=media.mediaid where cat_id='25' group by story.mediaid order by cnt desc limit 0,8

but i get only the images that has stories...i wanted all the result such that even if there is no story for a particular image we can show 0 stories...can we do that by not changing the table structure and just changing the sql statement?? Please help...-

Answer 1

the right join is ok, the group by story is the problem: you want to group by images, don't you?

try group by media.mediaid instead!

Answer 2

You need to flip your SQL and use LEFT JOIN instead:

SELECT count(story.id) as cnt, media.mediaid, media.catid, media.userid, media.link
  FROM media LEFT OUTER JOIN story ON media.mediaid = story.mediaid
 WHERE media.catid = '25'
 GROUP BY media.mediaid, media.catid, media.userid, media.link
 ORDER BY cnt DESC