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

Mark
--
Nur noch bis 31.01.2010: DSL-Komplettpaket für 16,99 Euro/mtl.!*
http://portal.gmx.net/de/go/dsl02