Re: FN-FORUM: MySQL query help

date posted 6th August 2003 19:33

Aaah that's perfect, left outer join does the trick, thanks Andy!
Nice to see someone not affected by the heat :)

Niina



At 19:53 06.08.2003, you wrote:

>SELECT g.id, g.genre_name, g.description, count(s.id) as stories FROM genre
>g
>LEFT OUTER JOIN story s ON (g.id=3Ds.genre_id AND s.status=3D"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" =3D approved, "q" =3D queued (waiting approval), "x" =3D
>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=3D"a" and story.genre_id=3Dgenre.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
> >
> >
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > 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 =A346.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
> >
> >
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>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 =A346.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
>
>