Mysql IF NOT EXISTS then select

Question

i try to select all rows "labels" from a table by parent_id and language. Default language is en.

list = SELECT * FROM labels WHERE parent_id = x AND lang='de'

if( list.isEmpty() ){
   list = SELECT * FROM labels WHERE parent_id = x AND lang='en'
}

how to handle this using only one MySQL Statement?

many Thanks

Answer 1

Give this a try,

SELECT  *
FROM    labels
WHERE   parent_id = x AND
        lang = 
        (
            SELECT  lang
            FROM    labels
            WHERE   parent_id = x
            ORDER   BY FIELD(lang, 'en', 'de') DESC
            LIMIT   1 
        )

• SQLFiddle Demo

The FIELD() function returns the index of string in the list.

Here's using JOIN

SELECT  DISTINCT a.*
FROM    labels a
        INNER JOIN
        (
            SELECT  parent_id, lang
            FROM    labels
            WHERE   parent_id = 1
            ORDER   BY FIELD(lang, 'en', 'de') DESC
            LIMIT   1 
        ) b ON a.parent_id = b.parent_id AND
                a.lang = b.lang
WHERE   a.parent_id = 1 

• SQLFiddle Demo

Answer 2

Use where not exists and union all:

SELECT * FROM labels WHERE parent_id = x AND lang='de'
union all
SELECT *
FROM labels
WHERE parent_id = x AND lang = 'en' and
      not exists (SELECT * FROM labels WHERE parent_id = x AND lang='de')