Hi Michael, at first I didn't see how this could be a puzzle at all, but upon reading a few replies, I understood that you wanted an exact match between two sets.

Well, I have a different solution to your problem that includes one additional table. This is done for two reasons:

1) Make it possible to use the same statement every time
2) Save myself from complexity that wreak havoc with my brain cells

First, some DDL:

CREATE GENERATOR MYREQUESTGEN;

CREATE TABLE MYREQUESTS
(
MYREQUESTKEY INTEGER NOT NULL,
PTYROLE Varchar(6) NOT NULL,
PARTIESKEY Bigint NOT NULL
);

You may want to create indexes, I don't know how often you will perform your query, so they may or may not be required.

(Normally, I would add another generator as well together with an ID column for a primary key and use a before insert trigger to fill it in, but this is not of importance for your problem, so I left it out.)

Now, each time you want to check for an identical set, start by getting the next value of MYREQUESTGEN from your Java application, and then insert your list of values into MYREQUESTS, all with the same value as you got from MYREQUESTGEN.

Then you can issue this SELECT, again using the value you got from MYREQUESTGEN as your parameter:

SELECT DISTINCT pg.PTYGROUPKEY
FROM PARTYGROUP pg
JOIN MYREQUESTS mr ON pg.PTYROLE = mr.PTYROLE
AND pg.PARTIESKEY = mr.PARTIESKEY
WHERE mr.MYREQUESTKEY = :MyRequestKey
AND NOT EXISTS(
SELECT null
FROM PARTYGROUP pg2
LEFT JOIN MYREQUESTS mr2 ON pg2.PTYROLE = mr2.PTYROLE
AND pg2.PARTIESKEY = mr2.PARTIESKEY
AND mr.MYREQUESTKEY = mr2.MYREQUESTKEY
WHERE pg.PTYGROUPKEY = pg2.PTYGROUPKEY
AND mr2.MYREQUESTKEY is null)
AND NOT EXISTS(
SELECT null
FROM MYREQUESTS mr3
LEFT JOIN PARTYGROUP pg3 ON pg3.PTYROLE = mr3.PTYROLE
AND pg3.PARTIESKEY = mr3.PARTIESKEY
AND pg.PTYGROUPKEY = pg3.PTYGROUPKEY
WHERE mr.MYREQUESTKEY = mr3.MYREQUESTKEY
AND pg3.PTYGROUPKEY is null)

What this select basically does, is to return all PTYGROUPKEY for which there's no record in either of the sets that isn't also in the other. I think this double negation would be the way to find an identical set (or maybe Firebird has some newish operator that I do not know about?).

I have tried this SQL on a tiny set of records, and I did get the result I expected. Whether this will be a feasible solution in your application or not, I expect to depend on the selectivity of PTYGROUPKEY.

HTH,
Set

--- In firebird-support@yahoogroups.com, Michael D. Spence 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.