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
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