# Choose which satisfies a minimum of N conditions from M available

there's an issue sailed. It's occur fundamental SQL terms, nevertheless its character is pure math (so perhaps I ought to visit http://mathoverflow.com too).

I've got a table in certain theoretical database with 6 fields, each one is amounts. Also we've fundamental conditions, for example Area_1 > Area_5, Area_4 = 3 etc., 7 conditions total. I have to write a choose, which satisfies a minimum of 4 of these.

Writing looooooooooooooooooooooooooong choose with lots of logical conditions for example *(cond_1 AND cond_2 AND cond_3 and cond_4) OR (...)* isn't a way, because 4-combination from 7 elements is equivalent to 140, and something does not wish to write a lot of conditions.

So how do you write a choose in the simplified form?

Just one way of doing the work would be to count 1 for every condition the row satisfies and compare the sum for 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
``````

Observe that this can require evaluating all of the conditions for every row which means you will not obtain a short-circuiting effect, but it is easy and maybe it's adequate performance for you personally.

If you are using MySQL you are able to write this inside a easier way just because a boolean result is the same as or 1 so you do not need the Situation claims:

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

You can simply add the sum 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 can even place the nastiness of this sum-of-cases expression right into a function for a little more readable code.

I'd suggest simply counting up the number of the weather is satisfied. The specific syntax will rely on which database management engine you are using, however in MySQL it might look something similar to 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;
``````