This is better because it can be parameterized,
but still has some of the same issues. Also,
suppose the values aren't really BETWEEN two
values. Or rather, while there's always an
upper and lower bound, all of the values in
between may not be represented.

To further illustrate my quandary, let's ignore
PARTIESKEY for now (or assume that it always has
the necessary values). Suppose that PTYROLE can
have the values A,B,C,D,E.

Also, suppose I already have group 1 with B, C, D
and group 2 with B, D.

Now suppose I have formed another group x, containing
B and D and I want to see if it's already represented
in the database. Using BETWEEN will fetch both group
1 and group 2.

Also, generating a series of WHERE clauses:

WHERE PTYROLE = B
OR PTYROLE = D

will do the same thing. What I really need, I
think, is some way to include a count:

WHERE (PTYROLE = B
OR PTYROLE = D)
AND (<number of items returned> = 2)

but I don't see a way to do this. A UDF or a stored
procedure might work but neither is allowed by the
situation.

And yes, I do have indices on PTYROLE and PARTIESKEY
because this is a largish dataset (~100000 rows)
and over time could be very much larger (10E7 rows).

This table is loaded into memory at startup, but of
course if it grows sufficiently, I'll have to do the
LRU thing, so when I get a cache miss, I was hoping
for a single, quick select to find the target data
in the database proper. So generating the SQL each
time then post-processing the result would only be
done on a cache miss, which isn't as bad as it might
be.

Thanks, though.

Michael D. Spence
Mockingbird Data Systems, Inc.

> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of amoradell
> Sent: Saturday, March 14, 2009 8:48 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: SQL Puzzle of the Day
>
>
>
> If you can filter with your app, you could try :
>
> SELECT PTYGROUPKEY, PTYROLE, PARTIESKEY FROM PARTYGROUP
> WHERE (PTYROLE between :minptyrole and :maxptyrole)
> and (PARTIESKEY BETWEEN :minpartieskey and maxpartieskey)
>
> You have to find the four values and pass them to you query
> and therefore filter the pairs in your app.
>
> And do you have index on PtyRole ? and PartiesKey ?
>
> It could be significant on large datasets.
>
> Regards
>
> Alexandre
>
> --- In firebird-support@yahoogroups.com,
> "mspencewasunavailable" <firebird@...> wrote:
> >
> > I have the following table:
> >
> > CREATE TABLE PARTYGROUP
> > (
> > PTYGROUPKEY Bigint NOT NULL,
> > PTYROLE Varchar(6) NOT NULL,
> > PARTIESKEY Bigint NOT NULL,
> > CONSTRAINT PK_PARTYGROUP_1 PRIMARY KEY (PTYGROUPKEY,PTYROLE)
> > );
> >
> >
> > For each PTYGROUPKEY, there are 1 or more unique
> PTYROLE,PARTIESKEY combinations. In my Java code, I have a
> list of these and would like to find the PTYGROUPKEY that
> exactly matches this list. I know I can do something like
> >
> > SELECT PTYGROUPKEY FROM PARTYGROUP
> > WHERE (PTYROLE = 'A' AND PARTIESKEY = 5)
> > OR (PTYROLE = 'B' AND PARTIESKEY = 132)
> > OR ....
> >
> > But (a) I'd rather use a prepared query and (b) this
> doesn't exclude cases where my list is actually a smaller
> subset of the set I retrieve with this statement.
> >
> > Thanks,
> >
> > Michael D. Spence
> > Mockingbird Data Systems, Inc.
> >
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>