MySQL query equal to one value or another

Question

MySQL query equal to one value or another

Navigation

#1 by (7 votes)

7

I have a query in a query of MySQL.

I have this query

select cor from cores where cor in('verde','vermelho');

The problem is that I want the green, and if it does not have green I want the red. but not the 2 results.

I can not use LIMIT 1 because this is within a more complex subquery and MySQL does not let me use LIMIT in subquery . >

mysql

asked by anonymous 05.02.2014 / 12:37

1 answer

How to remove an element from an XML with Python? Export Google Sheets data to the R

score 7 · Accepted Answer

Instead of the IN operator, you can use CASE to check if a record exists. It's like doing IF in a structured language.

See an example:

SELECT
  CASE 
     WHEN EXISTS(select cor from cores where cor = 'verde') THEN 'verde'
     ELSE (select cor from cores where cor = 'vermelho')
  END,
  CASE 
     WHEN EXISTS(select cor from cores where cor = 'azul') THEN 'azul'
     WHEN EXISTS(select cor from cores where cor = 'verde') THEN 'verde'
     ELSE (select cor from cores where cor = 'vermelho')
  END

Alternatively, you can use the COALESCE function, which will return the first non-null parameter, thus setting an order of priority.

See the example:

select
  COALESCE(
    (select cor from cores where cor = 'azul'),
    (select cor from cores where cor = 'verde'),
    (select cor from cores where cor = 'vermelho')
  )

See also sqlfiddle with functional examples.