Re: JOIN and aggregate problem

On Fri, 20 Feb 2009 19:06:48 +0000
Richard Huxton <dev@archonet.com> wrote:

> Tarlika Elisabeth Schmitz wrote:
> > I have 2 tables T1 and T2
> > 
> > T1 has the columns: D, S, C. The combination of D,S,C is unique.
> > T2 has the columns: D, S, C, and boolean X. The combination of
> > D,S,C is not unique. 
> > 
> > I need to produce the following result for every occurrence of T1:
> > D,S,C, COUNT
> > 
> > COUNT is the number of matching D,S,C combinations in T2 where X =
> > true. There might be no matching pair in T2 or there might be match
> > but X is false.
> 
> 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.



--


Best Regards,

Tarlika Elisabeth Schmitz


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