Re: JOIN and aggregate problem

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

--  Richard Huxton Archonet Ltd