MySQL will replace the result value with another value

Question

I have MySQL and a query:

select name,re_type from myTable 

I want to replace all values ​​of type:

 1 = student 2 = teacher 

Thus, the result should be:

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

I do not like:

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

Is it possible to make such a query to get the desired result in MySQL?

Answer 1

You can use the 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 

Answer 2

You can use a shared table to store tags for your re_type type, for example

 re_type_id re_type_label 1 student 2 teacher 

And change your request to:

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

Answer 3

I think he wants to contain the identifiers in the re_type field and just decode them when retrieving.

You can use CASE WHEN or ELT (), MySql equivalent to Oracle Decode (), as described here

but it’s best practice to create an external table containing the re_type and re_type_description fields, so if you have new values ​​tomorrow, you don’t have to change all your queries.