1

I currently have this code which is looking for the same values in multiple columns, is there a way to condense this code so it is not so lengthy?

SELECT DISTINCT
    client_ID
FROM 
    df1
WHERE 
    code_1 IN ( 'A', 'B', 'C', 'D', 'E')
    OR code_2 IN ( 'A', 'B', 'C', 'D', 'E')
    OR code_3 IN ( 'A', 'B', 'C', 'D', 'E')
    OR code_4 IN ( 'A', 'B', 'C', 'D', 'E')
    OR code_5 IN ( 'A', 'B', 'C', 'D', 'E')

My attempt which doesn't seem to be working:

SELECT DISTINCT
    client_ID
FROM 
    df1
WHERE 
    (code_1 OR code_2 OR code_3 OR code_4 OR code_5 IN ( 'A', 'B', 'C', 'D', 'E'))
GMB
  • 216,147
  • 25
  • 84
  • 135
Trevor M
  • 89
  • 9
  • 2
    Having numbered columns is a schema smell. You should consider fixing your schema to have a cross-reference table where multiple codes can be assigned by foreign key. – Daniel Mann Nov 18 '22 at 18:24

1 Answers1

1

In SQL Server, where tuple equality is not supported, an alternative to the long is of OR is to unpivot the columns to rows in an exists subquery:

select client_id
from df1
where exists (
    select 1 from ( values (code_1), (code_2), (code_3), (code_4), (code_5) ) x(code)
    where x.code in ( 'A', 'B', 'C', 'D', 'E')
)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • This is super helpful! Thank you so much. Quick follow up, can use additional ands/or's when using this method? For example, if I wanted to do ...where x.code in (A, B, C, D, E) OR other_column in (X, Y, Z)? – Trevor M Nov 18 '22 at 23:05
  • 1
    @TrevorM: you can simply add more conditions to the `where` clause. If you need to unpivot and check other columns, then you can also add more `exists` conditions. – GMB Nov 18 '22 at 23:19