Author |
Message |
21/11/2021 23:02:53
|
libonati
Joined: 26/07/2010 22:26:07
Messages: 3
Offline
|
Hi,
I'm doing an INNER JOIN between geonames table and alternatenames table using the language as variable.
I'm having troubles to get the right results since sometimes PPLs' alternateName have only the english version. As an example, if I want results for PPLs in italian, sometimes I have the italian version of the name, someothers I have only the english name. Moreover I need to order by isPreferredName DESC to avoid gather results like Big Apple instead of New York.
So I have to retrive results from an INNER JOIN between geonames table and alternatenames table, taking the italian name when available or the english name when italian is not available, ordered by alternateName DESC and by isPreferredName DESC
As an example of query limited to New York (but I don't need to filter by geonameid, but retrive all places):
SELECT g.geonameid, alternateName FROM geoname_new g INNER JOIN alternatename_new a ON g.geonameid=a.geonameid WHERE (isoLanguage='it' OR isoLanguage='en') and country='us' and g.geonameid=5128581 GROUP BY geonameid ORDER BY alternateName DESC, isPreferredName DESC LIMIT 0,10
The result is Big Apple...
Any suggestion on how to sort this out?
Best,
L.
|
|
|
13/12/2021 22:25:52
|
rouz
Joined: 29/11/2021 10:03:24
Messages: 2
Offline
|
Hi Libonati,
I think the best way is to use a subquery with ORDER BY FIELD :
SELECT gp.geonames_id, gp.name,
(SELECT ga.name
FROM geonames_alternates ga
WHERE ga.geonames_id = gp.geonames_id
ORDER BY FIELD(ga.lang_code, 'it', 'en', null) DESC LIMIT 1) AS alt_name
FROM geonames_places gp
LIMIT 10;
Best regards
|
|
|
|
|
|