How about this? CREATE TABLE t1(d INT,s INT, c INT);
CREATE UNIQUE INDEX idx01_t1 ON t1 USING btree (d, s, c); INSERT INTO t1 (d, s, c) VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5); CREATE TABLE t2(d INT,s INT, c INT, x boolean); INSERT INTO t2(d, s, c, x) VALUES (1,1,1,TRUE),(1,1,1,FALSE),(2,2,2,FALSE),(4,4,4,FALSE),(4,4,4,FALSE),(5,5,5,TRUE),(5,5,5,TRUE); SELECT t1.d, t1.s, t1.c, CASE WHEN t2.x IS NULL THEN 0 ELSE COUNT(*) END FROM t1 LEFT OUTER JOIN t2 ON t1.d = t2.d AND t1.s = t2.s AND t1.c = t2.c AND t2.x = TRUE GROUP BY t1.d, t1.s, t1.c,t2.x; --DROP TABLE t1; --DROP TABLE t2; On Fri, Feb 20, 2009 at 12:04 PM, Tarlika Elisabeth Schmitz <postgre...@numerixtechnology.de> 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. > > How can I express this? > > > > -- > > > Best Regards, > > Tarlika Elisabeth Schmitz > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql