On Fri, 20 Feb 2009 19:06:48 +0000 Richard Huxton <d...@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? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
