JOIN on set of rows?

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