pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Trouble with massive select statement.
Date: Tue, 22 Jun 1999 18:47:20 +0300
Message-ID: <l03130306b3956077cea4@[147.233.159.109]> (raw)

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





view thread (5+ messages)

Message-ID: <l03130306b3956077cea4@[147.233.159.109]>
Permalink:  ../l03130306b3956077cea4@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130306b3956077cea4@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il
  Subject: Re: [SQL] Trouble with massive select statement.
  In-Reply-To: <l03130306b3956077cea4@[147.233.159.109]>
  References: <l03130306b3956077cea4@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox