On 28/01/2010 10:47, Mark Rotteveel wrote:

>>>> Using coalesce might be easier:
>>>>
>>>> SELECT COALESCE(T2.ID, T1.ID)
>>>> FROM table T1
>>>> LEFT JOIN table T2 on T1.CODE_ID = T2.CODE_ID and T2.LANG_CODE =
>>>>
>> 'FR_fr'
>>
>>>> WHERE T1.LANG_CODE = 'EN_en'
>>>>
>>
>>> Just FYI, yes, that works too but is much slower then the union or the
>>> select shown by Svein and Sean.
>>>
>> That's a surprise, I would expect the speed to be similar - at least if
>> you changed to
>>
> I was surprised too, might be fixed by an addition index though.
>
>
>> SELECT COALESCE(T2.ID, T1.ID)
>> FROM table T1
>> LEFT JOIN table T2 on T1.CODE_ID = T2.CODE_ID and T2.LANG_CODE||'' =
>> 'FR_fr'
>> WHERE T1.LANG_CODE = 'EN_en'
>>
>> Though there are two other drawbacks with this solution:
>> 1) Those that are only defined with LANG_CODE 'FR_fr' and not 'EN_en' will
>> not be included in the result set.
>>
> I assume that English translations are always available,

Correct (maybe not English, but a language), that will be enforce by the
application.

> otherwise a full outer join with an additional join condition on T1.LANG_CODE='EN_en' should do the job (I think, not tried it).
>
>
>> 2) You will have to use COALESCE for all fields. Fine if you select one or
>> two fields, less readable if you select hundred fields.
>>
> True, it might be better and easier to simply load all languages (or always English and the required language) and leave the language selection to the application.
>

I am actually planning to use this in a Python application using
SQLAlchemy as the ORM.

I don't want the applications higher level (e.g. a listctrl have figure
out which language to use, I think it will be much easier if I can
define this in the model.

Thanks
Werner