Ashraf Bashir Ashraf Bashir - 4 months ago 27
MySQL Question

Mapping values in SQL select?

I have a table, lets for simplicity call it

Plant
,
with three columns:
id
,
name
,
category
.

This the most simplified example, so please do not worry about normalization ...

+-------+--------+------------+
| id | name | category |
+-------+--------+------------+
| 1 | orange | fruits |
| 2 | banana | fruits |
| 3 | tomato | vegetables |
| 4 | kaokao | NULL |
+-------+--------+------------+


If want to have a query which returns:


  • '
    Fruit Plant
    ' instead of '
    fruits
    '

  • '
    Vegetable Plant
    ' instead of '
    vegetables
    '

  • '
    unknown
    ' instead of
    NULL
    s



So the return should be:

+-------+--------+-----------------+
| id | name | category |
+-------+--------+-----------------+
| 1 | orange | Fruit Plant |
| 2 | banana | Fruit Plant |
| 3 | tomato | Vegetable Plant |
| 4 | kaokao | unknown |
+-------+--------+-----------------+


How can I do this mapping for select values ?

I am using mysql, if this may have a special
IF
keyword/function in mysql

Answer

You can use case expression:

select
    id,
    name,
    case 
        when category = 'fruits' then 'Fruit Plant'
        when category = 'vegetables' then 'Vegetable Plant'
        when category is null then 'unknown'
    end as category
from Plant