Background:

I have a Firebird 1.5.2 database that I am accessing using Visual
C++ (.NET 2003) and using SQLAPI for DB access
(http://www.sqlapi.com).

I have 2 tables in a master-detail relationship, and the master
table has a fair bit of information in it that I want to return in
the result set. However, the query I want to perform is based on
information in the child table.

Let's say TableMaster has:

MID Desc Many other columns as well...
1 Desc1
2 Desc2
3 Desc3

And TableChild has:

CID MID SomeValue
1 1 A
2 1 B
3 2 A
4 2 C
5 3 B
6 3 C

For this contrived example, each master record has 2 child records
associated with it.

So... I want to return in my result set all TableMaster records
where there is an associated child record with SomeValue in (A, B).

BTW, A, B, C are actually numbers... think of them as variable
values.

First try:

SELECT M.*
FROM TableMaster M
INNER JOIN TableChild C ON M.MID = C.MID
WHERE C.SomeValue IN (A, B)

This will return all 3 Master records, but the result set actually
has 4 items; the record with MID=1 is returned twice since there are
2 entries in the child table for MID=1 (one each for A and B). I
only want *distinct* instances of M...

... So, the simple workaround is to add a DISTINCT to get "SELECT
DISTINCT M.* ...", and this works, but it's S-L-O-W since DISTINCT
evaluates based on all returned columns (in M.* there are quite a
few).

What i'd rather do is to have DISTINCT work on a small amount of
data (the MID), so I try something like this...

SELECT M.*
FROM TableMaster M
WHERE M.MID IN (SELECT DISTINCT C.MID FROM TableChild C
WHERE C.SomeValue IN (A, B))

But this too, is inefficient and not at all scalable because of the
first IN clause, which may be quite large.

What i'd REALLY like to do is to join to a SELECT statement's result
set... something like (pseudo code... you get the idea):

SELECT M.*
FROM TableMaster M
INNER JOIN (SELECT DISTINCT C.MID AS Foo
FROM TableChild C
WHERE C.SomeValue IN (A, B)) X ON M.MID = X.Foo

I believe there are some DBs (MSSQL Server for example) that support
this syntax rather efficiently, but Firebird doesn't support SELECTs
in a FROM clause...

Another option is to put the inner subquery into a selectable stored
procedure and then INNER JOIN to it (a fine Firebird feature)...
but... somehow I'd have to pass the SP an array to fill in the IN
(A, B) clause of the subquery. (A, B) is just an example here; this
list could be 1 to 100 items.

So... i'm a bit stuck as to how to improve this...

Any help from the experts out there would be great!

Thx!

Greg