Re: JOIN and aggregate problem

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.

--
Richard Huxton
Archonet Ltd