CODEWITHSUNDEEP
phpmysqlsql
Pomio
Pomio

Reputation: 3

Select with custom result

I have in database:

City:
id | name       | type
1  | London     | A
2  | Manchester | E
3  | Cardiff    | W

and i would like receive with select:

1  | London     | First
2  | Manchester | Second
3  | Cardiff    | Third

so query should replace A to First, E to Second and W to Third.

SELECT id, name, type ???? FROM City

How can i make it?

Upvotes: 0

Views: 47

Answers (3)

Joel Jaime
Joel Jaime

Reputation: 469

SELECT
    id,
    name,
    IF(
        type = 'A', 'First',
    IF(
        type = 'E', 'Second',
    IF(
        type = 'W', 'Third', type))
) as type FROM City

I did a performance test and in this case several IF are faster than a WHEN

Upvotes: 0

Sebastian Schmidt
Sebastian Schmidt

Reputation: 1078

You should have a third table with the information for type. 

 type | label
 A    | First
 B    | Second
 C    | Third

Having this tables you can easily join the needed information. 

SELECT id, name, label FROM City LEFT JOIN `type_table` USING `type`

Upvotes: 1

juergen d
juergen d

Reputation: 204756

select id,
       name,
       case when type = 'A' then 'First'
            when type = 'E' then 'Second'
            when type = 'W' then 'Third'
       end as alias_name
from City

Upvotes: 2

Related Questions