On 28/01/2010 02:16, Leyne, Sean wrote:
> Werner,
>
>
>> Sven's worked fine to with the
>> example given, but if I throw some more rows, where there is e.g. a
>> "EN_en" with a corresponding "DE_de" but no FR_fr, then Sven's solution
>> is showing both the "EN_en" and the "DE_de".
>>
> Sven's SQL can easily be modified to correct for that issue.
>
I thought it could but these kind of constructs are still over my head.

Thanks for having taken the time to give me the solution.
> select *
> from MyTable M1
> where
> M1.LANG_CODE5 = 'FR_fr'
> or (
> M1.LANG_CODE5 = 'EN_en'
> AND NOT EXISTS(
> SELECT 1 FROM MyTable M2 where M2.CODE_ID = M1.CODE_ID and M2.LANG_CODE5 = 'FR_fr'
> )
> )
>
> Personally, I think the above/single select is cleaner/better than a UNION -- it will also be faster.
>
On a small table they perform the same, i.e. about 15ms. Will do some
more testing and experimenting with both of them and see how they do
after been translated to SQLAlchemy syntax/form.

Werner