Selecting e i t h e r row

[BarryTc]

Hello,
I want to select one row with a value 'us-en' if it exists and the row 'us' if the previous row does not exist.

The query goes a bit like this :
----

SELECT t1.a, t1.b, t2.a, t2.b
FROM t1,t2
WHERE t1.a = t2.a
AND t2.b = 'us-en' OR t2.b = 'us'
ORDER BY t1.b DESC

----
wich returns both rows. I would like MySQL to return just one row based on the existence of a row that contains 'us-en'.

"Group by" does not seem to be the right solution.
Can you help me?

Thanks a lot.

[r937]

yes, we can help you

regarding whether something exists or not, are you talking about NULL in a field or a separate row?

perhaps you could show a few rows of each table that illustrate the problem and then show the results you'd like to get back

[BarryTc]

This will be a bit more understandable :

I have a first table t1

text_id      smallint(6)

->few rows :

1 
2

Then i have a translation table t2

translation_id     smallint(6)         
translation_text_id    smallint(6)     
translation_language_id    varchar(5)  
translation_translation      text

->few rows

1 2 en commentary
2 2 fr commentaire

translation_text_id is a foreign key to text_id

I would like Mysql to select ONE rows from t2 that is related to text_id=2 wich would be the row #2 from t2 (WHERE t2.translation_language_id = 'fr') if there is such a row otherwise row #1 wich I'm sure exists.

In other words : I would like to get the french translation row if it exists. Otherwise i would like to get the row that contains the english translation.
---
I'm using Mysql 4.1.3 on redhat. Subselect may be used.
---
Thanks a lot.

[r937]

since the english row will always be there, you can INNER join to it

since the french row may not be there, you must LEFT OUTER join to it

if the french row doesn't exist, COALESCE will flip to the english translation

select t1.text_id  
     , coalesce(fr.translation
               ,en.translation) as translation
  from t1
inner
  join t2 as en
    on t1.text_id      
     = en.translation_text_id  
   and en.translation_language_id = 'en'
left outer
  join t2 as fr
    on t1.text_id      
     = fr.translation_text_id   
   and fr.translation_language_id = 'fr'

[BarryTc]

Yes. THIS is clean. Thanks a lot.