MySQL Multiple condition on multiple columns in one case

Question

is that possible to get multiple condition on multiple columns in one case i tried using this.. it is not creating any error but result is not correct. should i use IF else?

SELECT 
  p.name,
  p.is_deleted,
( CASE WHEN pro.is_active = 1 THEN 'Active'
       WHEN pro.is_del = 1 THEN 'Not Active-Promotion Deleted'
       WHEN p.is_active = 0 THEN 'Not Active-Page Inactive'
       WHEN p.is_deleted = 1 THEN 'Not Active-Page Deleted'
  END) AS 'Status'
FROM
  page_promotions pro 
  INNER JOIN pages p 
    ON pro.page_id = p.pages_id

Expected and Current output

You need to consider the fact that CASE statements evaluate from first to last. As soon as a case is met, the statement will end. You may need to add additional clauses to each case, or reorder them, to achieve the desired result. — JohnHC
– JohnHC, Commented Oct 4, 2016 at 7:46

Muhammad Tahir · Accepted Answer · 2016-10-06 05:41:51Z

CASE statements evaluate from first to last. As soon as a case is met, the statement will end. You may need to add additional clauses to each case, or reorder them, to achieve the desired result.

This is the behaviour of SQL:

A CASE expression evaluates to the first true condition as soon as the case meet the condition it ends.
If there is no true condition, it evaluates to the ELSE part.
If there is no true condition and no ELSE part, it evaluates to NULL

Add some aditional conditions to every case or reorder it to get the proper result

in your case to get desire output

SELECT 
  p.name,
  p.is_deleted,
( CASE  WHEN p.is_active = 0 THEN 'Not Active-Page Inactive'
        WHEN p.is_deleted = 1 THEN 'Not Active-Page Deleted'
        WHEN pro.is_del = 1 THEN 'Not Active-Promotion Deleted'
        WHEN pro.is_active = 1 THEN 'Active'
  END) AS 'Status'
FROM
  page_promotions pro 
  INNER JOIN pages p 
    ON pro.page_id = p.pages_id

Collectives™ on Stack Overflow

MySQL Multiple condition on multiple columns in one case

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related