AmirW AmirW - 1 year ago 43
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?

Thanks

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