Re: JOIN on set of rows?

Peter Fein wrote:
>>>
>>>SELECT t1.symbol AS app_name, t2.outside_key AS app_id
>>>FROM t2 LEFT JOIN t1 ON t1.t2_id=t2.id AS my_join
>>>LEFT JOIN rows of arbitrary (app_name, app_id) ON
>>>my_join.app_name=rows.app_name AND my_join.app_id=rows.app_id

> Sorry, I kinda wrote that wrong. ;) What I really want is:
>
> SELECT rows of known, app-generated (app_name, app_id)
> INTERSECT
> SELECT t1.symbol AS app_name, t2.outside_key AS app_id
> FROM t2 LEFT JOIN t1 ON t1.t2_id=t2.id
>
> There are around a max of 50 rows in the first select and
> perhaps up to 1 million in the second.

Personally, I'd just generate the SQL clause on the fly:

SELECT
t1.symbol AS app_name,
t2.outside_key AS app_id
FROM
t1, t2
WHERE
t1.t2_id = t2.id
AND (
(t1.symbol='<name-val-01>' AND t2.outside_key=<id-val-01>)
OR
(t1.symbol='<name-val-01>' AND t2.outside_key=<id-val-01>)
OR
...
)
;

I'm assuming you don't really want a LEFT JOIN between t2/t1 since that
would mean you had null app_name's which the application-generated
values couldn't match anyway.

If you find the performance unacceptable, try inserting the 50 rows into
a temporary (or perhaps even permanent) table and joining against that.
--
Richard Huxton
Archonet Ltd