MySQL output strings based on the IF/ELSE CASE query conditions

Let's say there is such a table _ _ test_if_else.

enter a description of the image here

It is necessary to output the lines corresponding to two conditions.

In a "pseudo language" it would look something like this:


    // Если есть id проверяем поле lang и выводим строку начиная с первого условия
    // т.е. вывести строки с двумя идентификаторами, но если нет 'en' вместо нее вывести 'ru'
        if( id (1||2) in  table ) {
            if ( lang === 'en' in table ) {
                 return this.ROW_EN // если сошлось
            }
            else if ( lang === 'ru' in table ){
                 return this.ROW_RU // ... нет 'en' выводим 'ru'
            }
            else {
                return NULL
            }
        }

I tried CASE, but I didn't find anything like BREAK,
, and it also outputs all the lines with an additional field after THEN.

Failed examples:

Here you need to select a string with lang = 'en',
since she is the first in the condition.


SELECT *, 
CASE
  WHEN (lang = 'en' and id_not_unique = 1) THEN 1 
  WHEN (lang = 'ru' and id_not_unique = 1) THEN 1 
END AS `row`
from __test_if_else;

enter a description of the image here

And here choose lang = 'ru', since 'en' with id_not_unique=2 is missing.


SELECT *, 
CASE
  WHEN (lang = 'en' and id_not_unique = 2) THEN 1 
  WHEN (lang = 'ru' and id_not_unique = 2) THEN 1 
END AS `row`
from __test_if_else;

enter a description of the image here
In the examples, I specified only one ID, but in fact there are many of them.


    id_not_unique IN (1,2 .... 1253)


How can I output only the necessary fields of the table, with certain identifiers, but in the absence of the string 'en'(which is checked first), output the rows with 'ru'?

Author: Alexander Lonberg, 2018-11-01
Source

2 answers

SELECT *
FROM table
WHERE id = @id
ORDER BY lang = @lang DESC
LIMIT 1
 0
Author: Akina, 2018-11-01 18:56:18

So far, I have come to this result.

There is a table of documents with different translations.

enter a description of the image here

An Italian came in and requested documents(1-4) in his own language, but probably all translations are not available. We are trying to return it in the order of priority.


    SELECT N.id_not_unique, N.lang, N.document FROM __test_if_else AS N
    INNER JOIN
    (SELECT S.id_not_unique, S.lang FROM
        (SELECT  id_not_unique, lang, 
            CASE lang
                WHEN 'it' THEN 0
                WHEN 'en' THEN 1
                WHEN 'fr' THEN 2
                ELSE 3
            END AS success
        from __test_if_else  WHERE id_not_unique IN (1,2,3,4) AND lang IN ( 'ru','en','it','fr' )
        ORDER BY success ) AS S 
    GROUP BY S.id_not_unique) AS U 
    ON (U.id_not_unique = N.id_not_unique AND U.lang = N.lang)

We hope that the priority of his knowledge:

  1. italian (well, that's understandable)
  2. English
  3. French
  4. well, let him teach on" extreme" Russian

enter a description of the image here

Perhaps someone will write a shorter one.

PS Update.

Thanks for the tip Mike
After all, in the CASE, I just sorted the "lang" field, which can be sorted like this.

In general, it was reduced to this, and I decided to add an optional field "id", so that INNER JOIN compares only one variable - it will be faster.


[id][id_not_unique][lang][document]

SELECT N.id_not_unique, N.lang, N.document FROM __test_if_else AS N
INNER JOIN
(SELECT S.id FROM
    (SELECT  id, id_not_unique
    from __test_if_else WHERE id_not_unique IN (1,2,3,4,5/*8576*/) AND lang IN ( 'ru','en','it','fr' )
    ORDER BY field (lang, 'fr', 'en', 'it' ) DESC )  AS S 
GROUP BY S.id_not_unique) AS U 
ON N.id = U.id

I would like, of course, one SELECT, but if nothing, then ...

 0
Author: Alexander Lonberg, 2018-11-03 15:33:36