Re: JOIN and aggregate problem

On Mon, 23 Feb 2009 15:44:05 +0000
Richard Huxton <dev(at)archonet(dot)com> wrote:

> Tarlika Elisabeth Schmitz wrote:
> > On Fri, 20 Feb 2009 19:06:48 +0000
> > Richard Huxton <dev(at)archonet(dot)com> wrote:
> >> try something like:
> >>
> >> SELECT t1.d, t1.s, t1.c, count(*)
> >> FROM t1
> >> LEFT JOIN (
> >> SELECT d,s,c FROM t2 WHERE x
> >> ) AS t2_true USING (d,s,c)
> >> GROUP BY t1.d, t1.s, t1.c;
> >>
> >> Warning - not tested
> >
> > Many thanks for the quick reply.
> >
> >
> > This suggestion does not work as it returns a count of 1 even when
> > there are no rows in t2 that match (d,s,c) in T1.
>
> Ah, then rather than count(*) you'll want count(t2_true.d) so when you
> get a null because of no match it's not counted. You can use any
> column from t2_true.

Indeed, that works. Now I am spoilt for choice!

--

Best Regards,

Tarlika Elisabeth Schmitz

A: Because it breaks the logical sequence of discussion
Q: Why is top posting bad?