5

So I have a SQL statement of the following form with multiple WHERE clauses:

SELECT cols FROM table
WHERE
CONDITION1 OR
CONDITION2 OR 
...
CONDITIONN

I know that if I run this, then I will get all rows that satisfy at least one of the above n conditions.

But now, I want to return the rows such that at least k of the WHERE conditions are satisfied. Is there a way to do this in SQL without writing out all n Choose k subsets of the set of WHERE clauses?

Relma2
  • 108
  • 5

2 Answers2

3

This is a rather tedious way, but it should work:

SELECT cols 
FROM table
WHERE 
    CASE WHEN CONDITION1 THEN 1 ELSE 0 END +
    CASE WHEN CONDITION2 THEN 1 ELSE 0 END + 
    CASE WHEN CONDITION3 THEN 1 ELSE 0 END +
    ...
    >= N
;
Lamak
  • 69,480
  • 12
  • 108
  • 116
0
SELECT cols 
FROM table
WHERE 
 CASE status
   CASE WHEN CONDITION1 THEN 1
   CASE WHEN CONDITION2 THEN 1
END

http://a2znotes.blogspot.in/2012/12/control-flow-functions.html is a very good resource on MySQL if else, case statement. All are explained with examples.

NS009
  • 7
  • 1
  • 1
  • 5