Adding case statement to SQL Query

Question

For the following SQL query:

SELECT `bh`.`bug_id`, `p`.`name` AS `project`, `p2`.`name` AS `portfolio`, `u`.`username` AS `user`,FROM_UNIXTIME( `bh`.`date_modified` ) AS `modify_ts`,
`b`.`severity`, `b`.`priority`, `b`.`resolution`, `b`.`Status`
FROM `bug`.`mantis_bug_history_table` AS `bh`
LEFT JOIN `bug`.`mantis_bug_table` `b` ON `bh`.`bug_id` = `b`.`id`
LEFT JOIN `bug`.`mantis_user_table` `u` ON `bh`.`user_id` = `u`.`id` 
LEFT JOIN `bug`.`mantis_project_table` `p` ON `b`.`project_id` = `p`.`id` 
LEFT JOIN `bug`.`mantis_project_hierarchy_table` `ph` ON `b`.`project_id` = `ph`.`child_id`
LEFT JOIN `bug`.`mantis_project_table` `p2` ON `ph`.`parent_id` = `p2`.`id` 
WHERE `bh`.`field_name` = "status"
AND `bh`.`old_value` = 80
AND `bh`.`new_value` = 50

I want to return the value of the "b.severity, b.priority" as names. So currently they will return a value such as 20,40,60 for severity and priority would have the values 30,60,90. What I want to do is say if the severity value = 20 then call it sev1, 40 then call it sev 2 and similarly the same for priorities.

How would I go about making that change within the query above?

Thanks very much for any help

possible duplicate of [How do you perform an IF...THEN in an SQL SELECT?](http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select) — drneel, Aug 15 '14 at 12:15
Do you have a table that has the names that you want for the given values? — Gordon Linoff, Aug 15 '14 at 12:16

score 1 · Accepted Answer · answered Aug 15 '14 at 12:33

A joined lookup table would be more flexible, but hardcoded would look like this:

SELECT
      bh.*
    , CASE
            WHEN b.severity = 20 THEN 'Sev1'
            WHEN b.severity = 40 THEN 'Sev2'
            WHEN b.severity = 60 THEN 'Sev3'
            WHEN b.severity = 80 THEN 'Sev4'
            ELSE 'v.low'
      END AS SeverityLbl
    , CASE
            WHEN b.priority = 30 THEN 'Priority1'
            WHEN b.priority = 60 THEN 'Priority2'
            WHEN b.priority = 90 THEN 'Priority3'
            ELSE 'v.low' 
      END AS PriorityLbl

FROM mantis_bug_history_table bh
      LEFT JOIN mantis_bug_table b
                  ON bh.bug_id = b.id

no problem, its easy when you know how - and it will be for you once you do it a few times. — Paul Maxwell, Aug 15 '14 at 14:30

score 0 · Answer 2 · answered Aug 15 '14 at 12:16

0

use CONCAT() function with in SELECT clause to get b.severity, b.priority" as names

answered Aug 15 '14 at 12:16

user3568303

102
4

Adding case statement to SQL Query

2 Answers2