CODEWITHSUNDEEP
mysqlsqlcase
Curious_Bop
Curious_Bop

Reputation: 311

Adding case statement to SQL Query

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

Upvotes: 0

Views: 86

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

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

Upvotes: 1

user3568303
user3568303

Reputation: 102

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

Upvotes: 0

Related Questions