On 28/01/2010 10:21, Svein Erling Tysvær 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
>
> 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'
>

That is better in speed, but still a lot slower.

> 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.
> 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.
>

Point 2 is why I don't want to go that way.

Thanks
Werner