Search Postgresql Archives

Re: JOIN on set of rows?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux