On Fri, 20 Feb 2009 13:23:47 -0600 Bob Henkel <bob.hen...@gmail.com> wrote:
> CREATE UNIQUE INDEX idx01_t1 > ON t1 USING btree (d, s, c); > > [...] > > 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; > > [...] > > 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. Thank you very much for taking the time to help. This is what I had tried myself but it does not cover the cases where 1) (1,1,1) exists in T1 but not in T2 1) (1,1,1) exists in T1 and T2 but X = false As an aside: I see you use UNIQUE INDEX. I had created T1 with PRIMARY KEY (D,S,C) assuming that that would create a unique index. -- 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
