CODEWITHSUNDEEP
sqlwhere-clausenetezza
Relma2
Relma2

Reputation: 108

SQL select if a certian number of WHERE clauses satisfied

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?

Upvotes: 5

Views: 136

Answers (2)

NS009
NS009

Reputation: 17

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.

Upvotes: 0

Lamak
Lamak

Reputation: 70638

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
;

Upvotes: 3

Related Questions