Using Yahoo APIs, how to get list of locations matching certain prefix that have weather data available

I have an app that (among other things) uses Yahoo Weather API to display weather conditions for a location selected by user.

In the configuration dialog where user can enter the location, I'd love to offer autocompletion so that while user is typing location name, list of matching cities is suggested.

I can use YQL to fetch locations matching the prefix, i.e.:

select * from geo开发者_开发问答.places where text = 'Vie*'

but the problem is that not every location has a weather station associated with it and I'd love to skip these in my autocompletion list.

Using community tables (table called weather.woeid), following query will join previous query with the weather api, returning only locations that do have weather stations:

select location from weather.woeid where w in (select woeid from geo.places where text = 'Vie*')

This almost solves my problem, except for the fact that previous query (which produces same result as weather api call) doesn't return WOEID nor any kind of identifier I can use to directly query the Weather API after configuration. How can I capture the value of join parameter w? I tried something like select w, location ... but that doesn't seem to work.

Is there any other way to get list of locations (incl. WOEID) matching certain prefix that have weather data associated with them?

Afaik it is not possible with YQL to pass through values from the Sub-Select (the inner SELECT statement) to the outer SELECT, which I is what you want to do if I understand you correctly.

Based on your use case I want to propose another solution though: I assume that the list of locations that have a weather station associated with them is relatively static, meaning this list does not change very often. If that is the case then it would not be very optimal in terms of performance to regenerate that list every time with YQL. Instead I would generate that list offline, store it in a file or MySQL or elsewhere and then just use that static list to answer to the AJAX call of your autocomplete field.

The data in that static list could look something like this:

{
"Vienna" => 72342,
"Hamburg" => 12334,
...
}

Once the user has selected a location and pressed enter, then you can send the YQL query to weather.woeid to look up the current weather based on the WOEID.