Question 20542 in Mysql Question posted in
The official MySQL documentation can be found here.

Issue with Case Condition in My SQL Query – Mysql

I have a sample table where I have written Case statement.
where I’m not able to write the proper syntax.

SELECT 
    case
        when SECTION = 'A' then
            case
                WHEN (SECTION ='A' AND ROLE = ROLENUMBER) 
                THEN 'GOOD' 
                ELSE 'BAD'
            end
        else 
            case
                when SECTION <> 'A' and ROLE <> 0 and ROLE is not null and ROLENUMBER <> 0 and ROLENUMBER <> null and ROLENUMBER <> ROLE 
                    then 'GOOD' 
                    else  'BAD'  
            when SECTION <> 'A' and (ROLE = 0 or   ROLE IS null) and ROLENUMBER = ROLE 
                then 'GOOD' 
                else  'BAD'  
        end
FROM
    CLASSROOM

Apart from Case Condition is there anyother way to write like IF condition with in the Case condition.

I’m looking to write query like IF section = 'A' then execute 1st condition ELSE IF section <> 'A'then execute below condition.

Tags:

2

Answers


  1. 0

    Does this capture all your "good" conditions?

      case 
     when section = 'A' and role = rolenumber then 'good'
     when section <> 'A' and role <> 0 and role is not null 
       and rolenumber <> 0  and rolenumber <> null and rolenumber <> role then 'good'
     when section <> 'A' and (role = 0 or role is null) and rolenumber = role then 'good'
     else 'bad'
  end
    Login or Signup to reply.
  2. 0

    You can write it more simpler like this.

    SELECT 
    CASE
        WHEN SECTION = 'A' AND ROLE = 'ROLENUMBER' THEN 'GOOD'
        WHEN
            SECTION != 'A' AND ROLE  0
                AND ROLE IS NOT NULL
                AND ROLENUMBER  0
                AND ROLENUMBER  NULL
                AND ROLENUMBER  ROLE
        THEN
            'GOOD'
        WHEN
            SECTION != 'A' AND ROLE = 0
                OR ROLE IS NULL AND ROLENUMBER = ROLE
        THEN
            'GOOD'
        ELSE 'BAD'
    END
FROM
    CLASSROOM
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search