I have a requirement to select records in one table based on a count
of records from another table and I can't figure out how to structure
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.