|Subject||Re: RE: [firebird-support] select help needed|
> >> Using coalesce might be easier: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 =
> >> 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)I assume that English translations are always available, 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).
> FROM table T1
> LEFT JOIN table T2 on T1.CODE_ID = T2.CODE_ID and T2.LANG_CODE||'' =
> 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.
> 2) You will have to use COALESCE for all fields. Fine if you select one orTrue, 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.
> two fields, less readable if you select hundred fields.
Nur noch bis 31.01.2010: DSL-Komplettpaket für 16,99 Euro/mtl.!*