Re: JOIN and aggregate problem

On Fri, 20 Feb 2009 13:23:47 -0600
Bob Henkel <bob(dot)henkel(at)gmail(dot)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
> <postgresql(at)numerixtechnology(dot)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?