-1

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)
John Rut
  • 39
  • 1
  • 1
  • 3
  • 1
    Both Case and Group are reserved words in MySQL – Strawberry Feb 28 '21 at 10:18
  • For further help, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Feb 28 '21 at 10:20
  • I removed the conflicting database tags. Please tag only the database product you are really using –  Feb 28 '21 at 12:09
  • Sample data, desired results, and particularly examples of what you do and do not want to select would all be helpful. – Gordon Linoff Feb 28 '21 at 12:14

2 Answers2

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)
      )
forpas
  • 160,666
  • 10
  • 38
  • 76
0

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)
kundan
  • 203
  • 2
  • 7