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