I'm not sure the query with BETWEEN logic like this will cut it. Example:
It might bring back rows where ptyrole is A and mpartieskey is 132, if such
a thing could exist, and the information at hand doesn't say it can't.



What you could try is this:



SELECT PTYGROUPKEY FROM PARTYGROUP

> WHERE (PTYROLE || PARTIESKEY) IN (your list of concatenated values)

You might need to CAST partieskey as a character type in the concatenation?



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of amoradell
Sent: Saturday, March 14, 2009 5: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
<mailto:firebird-support%40yahoogroups.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.
>

[Non-text portions of this message have been removed]