AmirW AmirW - 1 year ago 49
SQL Question

Querying a database with any subset of a list of conditions

I have a MySQL database with a number of columns (say column0 .. column19).

I want to select all rows of the database which satisfy a condition of the form:

column0 < value0 AND column1 < value1 AND ... AND column19 < value19.

The catch is that I want to select rows which satisfy ANY 15 conditions out of the above.

I know I can do multiple queries will all possible combinations of 15 conditions, but I'm looking for a solution with only 1 query.

Is it possible with MySQL? Is there another database architecture which allows such queries?


Answer Source

You could use the below, but don't expect it'll perform particularly well!

WHERE (CASE WHEN column0 < value0 THEN 1 ELSE 0 END + 
      CASE WHEN column1 < value1 THEN 1 ELSE 0 END + 
      ... + 
      CASE WHEN column19 < value19 THEN 1 ELSE 0 END) >= 15
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download