MySQL replace result value with another value

Question

I have MySQL and the query is:

select name,re_type from myTable

I want to replace all values of type:

1 = student
2 = teacher 

So the result should be:

name    re_type
---------------
Ron     Student
Mac     Teacher

Not like:

name    re_type
---------------
Ron     1
Mac     2

Is it possible to make a query like that so I get the desired result in MySQL ?

Solution 1

You can use a CASE statement

SELECT name, CASE WHEN re_type = 1 THEN 'Student' WHEN re_type = 2 THEN 'Teacher' ELSE 'Donno' END AS re_type_text
FROM myTable 

Solution 2

I think he wants to keep IDs on re_type field, and just decoding them when extracting.

You can use CASE WHEN or ELT(), MySql equivalent to Oracle's Decode(), like explained here

, but the Best Practice is to create an external table containing re_type and re_type_description fields, so if tomorrow you'll have new values, you don't have to change all your queries.

Solution 3

You can use a joint table that will store labels for your re_type like

re_type_id  re_type_label
1           student
2           teacher

And alter your query by :

select t.name,l.re_type_label
from myTable t
inner join labelsTable l
    on l.re_type_id = t.re_type