Author |
Message |
20/10/2016 10:03:08
|
uzbbert
Joined: 18/10/2016 11:04:10
Messages: 2
Offline
|
I'm having trouble creating a query that matches a geonameid to a single record in the `alternatename` table. Basically when I retrieve a "DE" translation for some entity, I want my SQL join to only return 1 match.
However, the fields `alternatename.isShortName` and `alternatename.isPreferredName` does not seem to be properly filled out. I get multiple matches per geonameid on my query, but i would like to have the following situation (returning the first record that matches the criteria):
if isPreferredName=1, then use it.
else if isShortName=1, then use that.
else, use whatever match we can find for that language code.
Any clue on how to build this query while still allowing it to be performant on a large dataset?
|
|
|
21/10/2016 13:02:08
|
uzbbert
Joined: 18/10/2016 11:04:10
Messages: 2
Offline
|
it took 2 days for this post to get through the moderation. I've since found an answer. I'm not going to post it until this forum opens up the doors
|
|
|
27/06/2017 09:43:17
|
jonny512379
Joined: 11/02/2017 20:41:32
Messages: 10
Offline
|
Hello
I am currently having the same issue.
Without nesting selects, i dont think it is possible to do in SQL alone, as the if/else match is on the row not multiple rows.
You could use "COALESCE( isPreferredName, isShortName) AS name" to get either the isPreferredName or isShortName, but again this only works on single rows.
Unfortunately nesting selects will be slow for a large data set.
I am currently getting the duplicate results/rows, then filtering them in code. At presents i think this will be the fastest way...
I am interested to know how you did this?
I think maybe the best way would be to run a routine on alternatename to build a custom table for your needs (you can have nested selects in this routine
as it will only need to run on initial data import or monthly if you subscribe to the data feed/premium data). this routine will take hours to run, but if you set the server to do this at 3am, etc. this should be fine...
PS if you want me to write the nested select, please let me know.
|
|
|
|