Re: FN-FORUM: MySQL query help

date posted 6th August 2003 19:27

SELECT g.id, g.genre_name, g.description, count(s.id) as stories FROM genre
g
LEFT OUTER JOIN story s ON (g.id=s.genre_id AND s.status="a")
GROUP BY g.id

Not tested it, but it looks about right to me :)

Andy

----- Original Message -----
From: "Niina Talikka" [EMAIL REMOVED]
To: [EMAIL REMOVED]
Sent: Wednesday, August 06, 2003 7:46 PM
Subject: FN-FORUM: MySQL query help


>
> 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
>
>
> ==========
> fido.net are pleased to sponsor these Freelancers messages. For all
> your web hosting needs check out http://www.fido.net/ with hosting
> packages starting from just £46.95 a year including VAT.
> Free 0871 Fax2Email numbers also available online!
> http://www.fido.net/ - the internet made simple! - 0800 1072 666
>
> To advertise here: http://www.freelancers.net/advertising.html
>
>