|Subject||Re: [firebird-support] select help needed|
|Author||Werner F. Bruhin|
On 28/01/2010 10:21, Svein Erling Tysvær wrote:
>>> Using coalesce might be easier:That is better in speed, but still a lot slower.
>>> 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'
> Though there are two other drawbacks with this solution:Point 2 is why I don't want to go that way.
> 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.