On 05/11/05 08:22 AM CDT, Richard Huxton <dev@xxxxxxxxxxxx> 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@xxxxxxxxx 773-575-0694 Basically, if you're not a utopianist, you're a schmuck. -J. Feldman ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
