Set,

I solved it a different way, but since it depends on the
LIST() builtin I'm not particularly happy with it. And for
reasons I don't understand, the java SQL stuff changes the
size of one of the parameters arbitrarily, so that I get
truncation errors when I try to pass parameters.

I may investigate you method a bit tomorrow. The trouble is
that this could potentially be a big table over time (hence
the BIGINTs for the keys instead of INTS), so I'm not sure
how much time the outer joins might take even with appropriate
indexes.

Anyway, Thanks!

Michael D. Spence
Mockingbird Data Systems, Inc.

> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein
> Erling Tysvær
> Sent: Monday, March 16, 2009 8:25 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: SQL Puzzle of the Day
>
>
> 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.
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>