Hi
I have two tables with information about spoken languages (ie English, German, Spanish).
The first table holds language independent information about a language (Id#, ISO code etc).
The second table holds language dependent information about a language, the name of the language (German, Deutch, etc).
I need a select query where the resulting table lists all of the languages by name. The names of the languages in the list should be presented in a default language. If the name of the language isn't registered for the language used for presenting the listing, it should be presented in one of the other languages.
To clarify, here's the tables and the resulting table I'm looking for:
lang:
Id Iso
1 GER
2 ENG
3 ES
4 FRA
lang_l: (Ld is foreign key from table lang, LNId identifies the language used for the name)
Ld LNId Name
1 1 Deutch
1 2 German
2 2 English
2 3 Inglés
3 1 Spanisch
4 3 Francés
Resulting table using english as default language:
Name LNId
German 2
English 2
Spanisch 1
Francés 3
There isn't registered an english name for Spanish and French, so they are presented in one of the other languages. It doesn't matter which. In the final listing these will be listed in parenthesis.
Can anybody help me with a select query that produces this resulting table ?
(I'm using Mysql)
