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:
>>
>> Select
>> T1.ID
>> from
>> TheTable T1
>> where
>> T1.Lang_Code5 = 'FR_fr'
>>
>> UNION
>>
>> Select
>> T2.ID
>> from
>> TheTable T2
>> left outer join
>> TheTable T3
>> on T2.Code_ID = T3.Code_ID
>> and
>> T3.Lang_Code5 = 'FR_fr'
>> where
>> T2.Lang_Code5 = 'EN_en'
>> and
>> 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.

Werner