SQL: Conditional where with mutually involving all variables

Question

How can I write a specific double or (multiple) where statement condition for an individual group involving conditions that should be included for all variables. Let me show you.

    select 
    case, group, department, riskscore 
    from sometable 
    where department in ('A', 'B')  -- condition that count for group A, B
or (group = 'one' and riskscore>5.5) -- condition count for one
or (group = 'two' and riskscore>8.0) - condition count for two

As you can see the program will select all group one+ 5.5> and two + 8.0> but i need them to involve the A, B department as well.

I know you can write it as follows but i wonder whether i can write it separately where I won't have to duplicate all the info that counts for both groups.

select 
        case, group, department, riskscore 
        from sometable 
        where (group in ('A', 'B') and group in ('one') and riskscore>5.5)
or (group in ('A', 'B') and group in ('two') and riskscore>8.0)

Answer 1

You need logical AND between the condition:

department in ('A', 'B')

and the conditions:

(`group` = 'one' and riskscore>5.5) or (`group` = 'two' and riskscore>8.0)

like this:

where department in ('A', 'B')  
  and (     
           (`group` = 'one' and riskscore>5.5)
        or (`group` = 'two' and riskscore>8.0)
      )

Answer 2

you can go for nested query (it may have performance issues depending on situations. for example:

select required_columns 
from (
      SELECT required_columns 
      from some_table
      where department in ('A', 'B')
) Where (group = 'one' and riskscore>5.5) 
 or (group = 'two' and riskscore>8.0)