Nuevallorker Nuevallorker - 7 months ago 9
SQL Question

How to select all records where ANY two columns are above a certain value

I have a table with 50 columns. Looks like this

GUID Field1 Field2 Field3 .... Field50
---- ------ ------ ------ -------
1 0 1 0 0
2 1 0 0 1
3 0 2 3 1


I need to find all records where any two (or more) of the 50 columns are > 0. This would result in records 2 and 3 being returned.

I'm not really sure how to tackle this. If I were to try and test all the permutations it would be insane. There's got to be a better way.

I'm using SQL.

Answer

You probably have the wrong database design. When you have the same data in multiple columns -- only distinguished by a numerical suffix -- then that usually suggests that you really want a proper junction table.

In any case, you can do this, it is just a long case-based condition:

select t.*
from t
where ((case when field1 > 0 then 1 else 0 end) + 
       (case when field2 > 0 then 1 else 0 end) + 
       . . .
       (case when field50 > 0 then 1 else 0 end) + 
      ) >= 2;

You need to fill in the . . . with the additional 47 fields.

Comments