Re: JOIN on set of rows?

On 05/11/05 08:22 AM CDT, Richard Huxton <dev(at)archonet(dot)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(at)pobox(dot)com 773-575-0694

Basically, if you're not a utopianist, you're a schmuck. -J. Feldman