|Subject||Re: [firebird-support] select help needed|
|Author||Werner F. Bruhin|
On 27/01/2010 19:56, Mark Rotteveel wrote:
>> WFB> In other words I want all rows with "LANG_CODE5" = 'FR_fr", plus all
>> WFB> with "EN_en" where there is no corresponding "FR_fr".
>> Try the following. But I wrote that together quickly and am not sure
>> if it works as desired for you:
>> TheTable T1
>> T1.Lang_Code5 = 'FR_fr'
>> TheTable T2
>> left outer join
>> TheTable T3
>> on T2.Code_ID = T3.Code_ID
>> T3.Lang_Code5 = 'FR_fr'
>> T2.Lang_Code5 = 'EN_en'
>> T3.ID is null
> 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'
Thanks for this suggestion.
Just FYI, yes, that works too but is much slower then the union or the
select shown by Sven and Shaw.