MySQL, how can I retrieve a fields if another is empty?

I need to retrieve a field, only if is not empty:

I have a table with: counter(primary),id (this is the same for the same page), text, la开发者_运维知识库nguage and other fields: i have the id and the language.

I need to search the text in that language, and if it's empty I need the text in english. example: - 0,1,hello,eng - 1,1,ciao,ita In this case I need to retrieve "ciao", but if it's empty I need "hello".

How can I?

---

Well, I guess you could just get the Italian (if not empty) and the English and order with Italian first (if any):

SELECT
    text
FROM
    <table>
WHERE
    id = <page-id>
  AND (
      (
        language = '<preferred-language-to-retrieve>'
          AND
        text IS NOT NULL
      )
    OR
      language = '<fallback-language>'
  )
ORDER BY
    language = '<preferred-language-to-retrieve>' DESC
LIMIT 1

(untested)

With substituted values for Italian with English fallback for page ID = 75 (assuming table is named l10n):

SELECT
    text
FROM
    l10n
WHERE
    id = 75
  AND (
      (
        language = 'ita'
          AND
        text IS NOT NULL
      )
    OR
      language = 'eng'
  )
ORDER BY
    language = 'ita' DESC
LIMIT 1

---

try this :

SELECT text 
FROM table lcl
WHERE id=1 AND language='ita'
UNION ALL
SELECT text 
FROM table eng
WHERE id=1 AND language='eng'
LIMIT 1

The first select should always be the language specific check, the 2nd select will return the english version, and if you use limit 1 you will only ever get the first row. If the language specific version is missing you will end up with the value in the 2nd select, English

OR

SELECT 
    CASE WHEN 
    lcl.text IS NOT NULL THEN 
        lcl.text
    ELSE
        eng.text END AS text
FROM table eng
LEFT JOIN table lcl ON lcl.id=1 AND lcl.language='ita' 
WHERE eng.id=1 AND eng.language='eng'

This will get the english version (so assumes it is always there) and if it exists gets the selected langange version. In the select we simply have to display the local language version if it not empty or the english version if it is.