Select which satisfies at least N conditions from M available.

Question

Hi, there is a question floated. It's set in basic SQL terms, but its nature is pure math (so maybe I should visit http://mathoverflow.com too).

I have a table in some theoretical database with 6 fields, all are numbers. Also we have basic conditions, such as Field_1 > Field_5, Field_4 = 3 etc., 7 conditions total. I need to write a select, which satisfies at least 4 of them.

Writing looooooooooooooooooooooooooong select with many logical conditions such as *(cond_1 AND cond_2 AND cond_3 and cond_4) OR (...)* is not a way, because 4-combination from 7 elements is equal to 140, and one doesn't want to write so many conditions.

So how do I write a select in its simplified form?

Answer 1

One way of doing it is to count 1 for each condition that the row satisfies and compare the sum to your target value:

SELECT * 
FROM yourtable
WHERE (
          (CASE WHEN condition1 THEN 1 ELSE 0 END) +
          (CASE WHEN condition2 THEN 1 ELSE 0 END) +
          ...
          (CASE WHEN condition7 THEN 1 ELSE 0 END)
      ) >= 4

Note that this will require evaluating all the conditions for each row so you won't get a short-circuiting effect, but it's simple and maybe it has good enough performance for you.

If you're using MySQL you can write this in a much simpler way because a boolean result is equivalent to 0 or 1 so you don't need the CASE statements:

WHERE (condition1) + (condition2) + ... + (condition7) >= 4

Answer 2

You could simply add the sum of the true conditions together, a la

CASE 
WHEN Field1 > Field5 THEN
    1
ELSE
    0
END
+
CASE
WHEN Field4 = 3 THEN
    1
ELSE
    0
END
+
etc
AS condition_sum

and filter on condition_sum > threshhold. You could even put the nastiness of that sum-of-cases expression into a function for slightly more readable code.

Answer 3

I'd suggest simply counting up how many conditions are satisfied. The particular syntax will depend on which database management engine you're using, but in MySQL it would look something like this:

SELECT things
FROM places
WHERE IF(cond_1, 1, 0) + IF(cond_2, 1, 0) + IF(cond_3, 1, 0) + IF(cond_4, 1, 0) + IF(cond_5, 1, 0) >= 4;