Subject Re: Select how to
Author Ian A. Newby

> the SELECT statement.
> Suppose I have:
> table1 (table1_key varchar);
> table2 (table2_key varchar;
> table2_sequence_number integer;
> somefield : varchar);
> Is it possible to structure a SELECT statement that will retrieve all
> records from table1 such that COUNT(table2_sequence) WHERE table2_key
> = table1_key AND somefield = 'some value' is at most some arbirtrary
> value.
> I don't want to do a SELECT ... FROM table1 where table1_key IN (...)
> because that would involve doing a table scan on table2 and this would
> take far too long. I need to be able to do some kind of JOIN on
> table2 to limit the number of records scanned (I think).
> Any suggestions are appreciated.

How about

SELECT a.table1_key FROM table1 a JOIN table2 b ON a.table1_key =
b.table2_key WHERE b.somefield = 'some value'
GROUP BY a.table1_key
HAVING count(b.table1_sequence) > 5

It might work.