Thread: SELECT query

I can't figure out how to make the following query:

Table = circles_proc

subject     text,
arm          char,
rep            int,
cycle         int


I'd like to find the unique subject, arm, and rep tuples where the rep
has exactly 5 cycles associated with it.

I think it would be something like:
select distinct subject, arm, rep from circles_proc where rep = (select
rep  from circles_proc where 5 = count(cycle));

but the count is going to be performed over all tuples returned. I'd
like to have the count performed over each rep and just find the reps
where there are 5 cycles.

Can someone help?
Thanks.
-Tony

On Wed, 14 Mar 2001, G. Anthony Reina wrote:

> I think it would be something like:
> select distinct subject, arm, rep from circles_proc where rep = (select
> rep  from circles_proc where 5 = count(cycle));

You're heading towards

SELECT distinct subject, arm, rep FROM circles_proc WHERE rep IN
  (SELECT rep FROM circles_proc GROUP BY rep HAVING COUNT(rep)=5);

A possible variant would be

SELECT distinct subject, arm, rep FROM circles_proc c1 WHERE EXISTS
  (SELECT rep FROM circles_proc c2 WHERE c1.rep=c2.rep GROUP BY reg HAVING
   COUNT(rep)=5)

though w/a small set of test data, this seems less efficient.

There's also

SELECT DISTINCT subj, arm, rep FROM circles_proc c1 WHERE 5=
  (SELECT COUNT(rep) FROM circles_proc c2 where c1.rep=c2.rep);

but, again, EXPLAIN thinks this will be less efficient.


Unless you get a great solution elsewhere :-), try indexing the fields
and testing the different queries above.

Good luck,

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Joel Burton wrote:

> On Wed, 14 Mar 2001, G. Anthony Reina wrote:
>
> > I think it would be something like:
> > select distinct subject, arm, rep from circles_proc where rep = (select
> > rep  from circles_proc where 5 = count(cycle));
>
> You're heading towards
>
> SELECT distinct subject, arm, rep FROM circles_proc WHERE rep IN
>   (SELECT rep FROM circles_proc GROUP BY rep HAVING COUNT(rep)=5);
>

Thanks Joel! That works fine.

-Tony