Hi all,

Our data model has the classic many to many relationship handled as such

Create Table MyTable1
(
bigint id1 not null, // this uses a generator for auto assignment
// other stuff here
)

Create Table MyTable2
(
bigint id2 not null, // this uses a generator for auto assignment
// other stuff here
)

Create Table MyJoin
(
bigint joinid not null, // PK
bigint id1 not null, // FK
bigint id2 not null, // FK
// other stuff here
)

Quite often our application needs to find all items in the MyJoin table where id1 is in (some set of IDs). This set of IDs is arbitrary (i.e. selected by the user), and can potentially be large (> 500 items; the user selects a large range of items, and ctrl-clicks to deselect items, for example).

So, a query like

SELECT * from MyJoin
WHERE id1 in (val1, val2, ..., valn)

is going to have issues when n is large.

The way I implemented this in the past is to create a temporary table with 1 column (for the ID), fill it with the list, and join MyJoin to it to get my results. However, there is no such mechanism in Firebird to do this. I could create a real table and just drop it, but this becomes an issue if the connection disappears, or if multiple users need the same query; we would then need a random name for the table (or appended generator id like temp11)... but this seems to be a bit over the top to me for dealing with this.

What would be the 'recommended' way of doing this? Surely this is not a unique problem, so someone else's success story would be helpful!

thx!

Greg

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