Answers for "select with case in mysql"

mysql switch case

SELECT CASE lower(col1)
	WHEN 'agree' THEN 'Ok'
	WHEN 'disagree' THEN 'Ko'
    ELSE
        CASE 
            WHEN col2 = 1 THEN 'Ko'
            ELSE 'Maybe'
        END
END AS my_result
FROM table_name;

mysql switch case

mysql> SELECT CASE  WHEN 2>3 THEN 'this is true' ELSE 'this is false' END; 
+-------------------------------------------------------------+
| CASE  WHEN 2>3 THEN 'this is true' ELSE 'this is false' END |
+-------------------------------------------------------------+
| this is false                                               | 
+-------------------------------------------------------------+

mysql case

-- LABEL MOVIE LENGTH 
select title, length,
CASE 
	 when length < 50 then 'very short film'
     when length < 90 then 'short film'
     when length < 120 then 'medium length film'
     when length > 120 then 'long film'
     else 'unknown length'
END -- AS film_length
from film;

-- SAME AS ABOVE BUT ON A CUSTOM NAMED COLUMN
select title, length,
CASE 
	 when length < 50 then 'very short film'
     when length < 90 then 'short film'
     when length < 120 then 'medium length film'
     when length > 120 then 'long film'
     else 'unknown length'
END  AS film_length
from film;

-- RESULT
+-----------------------------+--------+--------------------+
| title                       | length | film_length        |
+-----------------------------+--------+--------------------+
| ACADEMY DINOSAUR            |     86 | short film         |
| ACE GOLDFINGER              |     48 | very short film    |
| ADAPTATION HOLES            |     50 | short film         |
| AFFAIR PREJUDICE            |    117 | medium length film |
| AFRICAN EGG                 |    130 | long film          |
| AGENT TRUMAN                |    169 | long film          |
| AIRPLANE SIERRA             |     62 | short film         |
| AIRPORT POLLOCK             |     54 | short film         |
| ALABAMA DEVIL               |    114 | medium length film |
| ALADDIN CALENDAR            |     63 | short film         |
| ALAMO VIDEOTAPE             |    126 | long film          |
| ALASKA PHANTOM              |    136 | long film          |
| ALI FOREVER                 |    150 | long film          |
| ALICE FANTASIA              |     94 | medium length film |
| ALIEN CENTER                |     46 | very short film    |

mysql switch case

SELECT 
t2.company_name,
t2.expose_new,
t2.expose_used,
t1.title,
t1.status,
 CASE status
   when 'New' and t2.expose_new = 1 then 1
   when 'New' and t2.expose_new = 2 then 2
   when 'New' and t2.expose_new = 3 then 3
   when 'Used' and t2.expose_used = 1 then 1
   when 'Used' and t2.expose_used = 2 then 2
   when 'Used' and t2.expose_used = 3 then 3
END as expose
FROM `products` t1
join manufacturers t2 on t2.id = t1.seller
where t1.seller = 4238

select with case in mysql

SELECT 
CASE
    WHEN Quantity > 30 
  THEN "The quantity is greater than 30"
    WHEN Quantity = 
  30 THEN "The quantity is 30"
    ELSE "The quantity is 
  under 30"
END
FROM OrderDetails;