[MySQL] use value B if value A not present

Databases
1

Hi all

Cant get my head around this one and I think it must be tiredness or (legal) drugs…

I have a table that has:


id (int) PK AI
menu_text VARCHAR(255)
language VARCHAR(5)

which holds data such as:


1 | home       | en
2 | about       | en
3 | contact    | en
4 | home       | de
5 | about       | de

What I am trying to achieve is to select menu_text based on language which I can do BUT if the row is not present - in this case contact | de, use the contact | en instead.

So it behaves like a default value
Pseudo code would be something like


select
      id
    , home
from
     mytable
where
     language = 'de'
if exists else 
     language = 'en'

I could write it outside of MySQL if needs be but wondered if there was a way to do it natively in MySQL

Cheers

Spike

2 
SELECT COALESCE(de.menu_text,en.menu_text) AS menu_text
  FROM daTable AS en
LEFT OUTER
  JOIN daTable AS de
    ON de.menu_text = en.menu_text
   AND de.language = 'de'
 WHERE en.language = 'en'

:cool: