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
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
