Re: [SQL] JOIN and aggregate problem

2009-02-23 Thread Tarlika Elisabeth Schmitz
On Mon, 23 Feb 2009 15:44:05 + Richard Huxton wrote: > Tarlika Elisabeth Schmitz wrote: > > On Fri, 20 Feb 2009 19:06:48 + > > Richard Huxton wrote: > >> try something like: > >> > >> SELECT t1.d, t1.s, t1.c, count(*) > >> FROM t1 > >> LEFT JOIN ( > >>SELECT d,s,c FROM t2 WHERE x > >

Re: [SQL] JOIN and aggregate problem

2009-02-23 Thread Richard Huxton
Tarlika Elisabeth Schmitz wrote: > On Fri, 20 Feb 2009 19:06:48 + > Richard Huxton wrote: >> 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 - no

Re: [SQL] JOIN and aggregate problem

2009-02-23 Thread Tarlika Elisabeth Schmitz
On Fri, 20 Feb 2009 13:23:47 -0600 Bob Henkel 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

Re: [SQL] JOIN and aggregate problem

2009-02-23 Thread Tarlika Elisabeth Schmitz
On Fri, 20 Feb 2009 19:06:48 + Richard Huxton 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. > > > >

Re: [SQL] JOIN and aggregate problem

2009-02-23 Thread Tarlika Elisabeth Schmitz
On Fri, 20 Feb 2009 11:15:09 -0800 (PST) Stephan Szabo wrote: > On Fri, 20 Feb 2009, 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,

Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Bob Henkel
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),

Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Bob Henkel
Scratch this one won't work for you. On Fri, Feb 20, 2009 at 1:03 PM, Bob Henkel wrote: > I might be missing something but does this solve your issue? > > 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,

Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Stephan Szabo
On Fri, 20 Feb 2009, 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 occurr

Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Richard Huxton
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

Re: [SQL] JOIN and aggregate problem

2009-02-20 Thread Bob Henkel
I might be missing something but does this solve your issue? 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); CREATE TABLE t2(d INT,s INT, c INT, x boolean); INSERT INTO t2(d, s, c, x

[SQL] JOIN and aggregate problem

2009-02-20 Thread Tarlika Elisabeth Schmitz
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 co