Re: JOIN and aggregate problem

Tarlika Elisabeth Schmitz wrote:
> On Fri, 20 Feb 2009 19:06:48 +0000
> Richard Huxton <dev@archonet.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