0

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, language 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?

Improve this question

2 Answers 2

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

it works perfectly for now :-) but i don't understand this: language = '<language-to-retrieve>' can you explain your query?
@Zak it was just my way of indicating that you needed to substitute to the desired language, e.g. language = 'ita' (both places).
no ok, i mean: why an association in the orderby? i knew only order by lang, not order by lang=ita
@Zak The expression gets evaluated for each row, 'ita' = 'ita' → 1, 'eng' = 'ita' → 0, thus sorting English above Italian (which was not the idea. I now realize that I'm either wrong on this assumption or that a DESC is needed :) )
The idea was to make sure that the Italian row was always before the English (assuming that the Italian text was not NULL). I fail to see how this can be guaranteed without the ORDER BY (but again, I may be wrong on this).
|
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.

Improve this answer

4 Comments

it works! but i need to make 2 linked query? there's no a better method?
ok, updated the answer to a more sub-query friendly version, (please note it is not tested)
it return only " field: CASE WHEN lcl.text IS NULL THEN lcl.text ELSE eng.text END value: NULL "
have aliased the column name (and corrected the condition in the case, it was the wrong way around)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.