Thread: JOIN on set of rows?
Hiya- I need to do something like this: 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 The arbitrary app_name, app_id come from my app ;). I can't figure out how to create something that acts like a table with rows specified in the text of the query. A temporary table perhaps? I don't know much (anything) about these... Thanks! -- Peter Fein pfein@pobox.com 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman
On 05/10/05 06:17 PM CDT, Peter Fein <pfein@pobox.com> said: > Hiya- > > I need to do something like this: > > 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 > The other thought was: 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 UNION SELECT rows of arbitrary (app_name, app_id) Thanks! -- Peter Fein pfein@pobox.com 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman
Peter Fein wrote: > Hiya- > > I need to do something like this: > > 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 > > The arbitrary app_name, app_id come from my app ;). I can't figure out > how to create something that acts like a table with rows specified in > the text of the query. > > A temporary table perhaps? I don't know much (anything) about these... OK - a few points. 1. I don't see any of your arbitrary columns used in the output of this query, which since they are on the outside of a left join means they don't have any effect. I'm assuming that's not what you want. 2. If the values are truly arbitrary, you might as well just generate random text and numbers in the query itself. So - are they user supplied values, or selections from a large set of possible values. 3. You don't say how many rows - 10, 100, 1000, 1 million? -- Richard Huxton Archonet Ltd
On 05/11/05 08:22 AM CDT, Richard Huxton <dev@archonet.com> said: > Peter Fein wrote: > > Hiya- > > > > I need to do something like this: > > > > 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 > > > > The arbitrary app_name, app_id come from my app ;). I can't figure > > out how to create something that acts like a table with rows > > specified in the text of the query. > > > > A temporary table perhaps? I don't know much (anything) about > > these... > > OK - a few points. > > 1. I don't see any of your arbitrary columns used in the output of > this query, which since they are on the outside of a left join means > they don't have any effect. I'm assuming that's not what you want. > 2. If the values are truly arbitrary, you might as well just generate > random text and numbers in the query itself. So - are they user > supplied values, or selections from a large set of possible values. > 3. You don't say how many rows - 10, 100, 1000, 1 million? 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. Basically, the generates a few pairs of (app_name, app_id) and needs the subset of those that already have corresponding records in t1. Sorry for the confusion, I'm still learning to think in terms of set operators... Thanks! -- Peter Fein pfein@pobox.com 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman
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
Peter Fein <pfein@pobox.com> writes:
> 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.
You could do it like
(
SELECT appname1, appid1
UNION ALL
SELECT appname2, appid2
UNION ALL
...
SELECT appnameN, appidN
)
INTERSECT
SELECT ...
The UNION ALL bit is a tad grotty, but it will do until someone gets
around to implementing the full SQL VALUES construct.
regards, tom lane