FN-FORUM: MySQL query help

date posted 6th August 2003 19:19

it could be the heat but I need some help with a MySQL query if anyone's
got a moment..

I have the following tables/fields:

genre
id
genre_name
description

story
id
genre_id
status ( "a" = approved, "q" = queued (waiting approval), "x" = rejected )


What I need is a result such as:
1 - Horror - this is a description - 1 story
2 - Romance - this is a description - 3 stories
3 - Film - this is a description - 0 stories
4 - Comedy - this is a description - 4 stories
5 - Kids - this is a description - 0 stories


Using the following query, it only shows me the genre information if the
genre has 1 or more approved stories (from above example, it would -not-
show genre 3 or 5):

query : select genre.id, genre_name, description, count(*) as stories from
genre, story where story.status="a" and story.genre_id=genre.id group by
genre.id


I can't figure out how to make it show those with 0 stories.


Help, please ! :)

Thanks,
niina
www.ktobe.com