Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA60541 for ; Tue, 22 Jun 1999 11:45:57 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id 98C644B50C; Tue, 22 Jun 1999 18:45:53 +0300 (IDT) Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA4F1 for ; Tue, 22 Jun 1999 18:48:43 +0300 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Tue, 22 Jun 1999 18:47:20 +0300 To: pgsql-sql@postgreSQL.org From: Herouth Maoz Subject: Re: [SQL] Trouble with massive select statement. At 15:08 +0300 on 22/06/1999, Darren Greer wrote: > See below > > > > select distinct username, station > > > from testmaster t1 > > > where 1 < ( > > > select count(t2.username) > > > from testmaster t2 > > > where t2.test_date > '05-14-1999' > > > and t2.station = 'WZZZ' > > > and t1.username = t2.username > > > ) > > > ; > > > > The above doesn't seem to me to do what you claim you want to do --- > > it seems to be looking for users who have taken the test *more than > > once* in the given interval. > That is correct......my mistake :) > > > Assuming that the code is right and the comment wrong ;-), I agree > Good assumption :) > > > SELECT username, station FROM testmaster > > WHERE test_date > '05-14-1999' and station = 'WZZZ' > > GROUP BY username, station > > HAVING count(*) > 1; > This works wonderfully, thanks. Now comes the other twist. I already have a > select statement (select first_name, email from listeners l, >listeners_data ld > where l.username = ld.username a nd $filter;). The $filter is a bunch of and > statements that limit the data coming from the table listeners. I need >to get > the select statement you told me to work as a filter on the data that this > select statement grabs. I cant see an easy way to do this without making >this > thing incredibly slow having to chech every user. Any thoughts? Perhaps you should go with your original scheme. Something like this: SELECT first_name, email FROM listeners l, listeners_data ld WHERE l.username = ld.username AND 1 < ( SELECT count (*) FROM testmaster t WHERE t.test_date > '05-14-1999' AND t.station = 'WZZZ' AND t.username = l.username ); Now, assuming there is an index on the username field in testmaster, the internal select will only look for the proper username in testmaster. I wouldn't say that this is very heavy for a subquery. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma