Question 21020 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Query With Combinatorial Where Clause

Let’s say I have a table with Columns A, B, C, D, E and F.
How would I query for entries where (A, B, C, D, E, F) = (1, 2, 3, 4, 5, 6) but only a subset of columns need to match? For example at least 3 out of the 6 columns have to match.

The only solution I can think of is to go through all combinations where (A, B, C) = (1, 2 ,3) or (A, B, D) = (1, 2, 4) or...

But in this example that would already be 20 where clauses, if my math is correct. Is there a better solution, that also works with more columns? Or is my only option to programmatically create a huge, non human-readable query string with hundreds of where clauses?

Tags:

2

Answers


  1. 0

    You can use a score system and then get the rows sorted by score. For example:

    select *
from (
  select t.*, 
    case when a = 1 then 1 else 0 end + 
    case when b = 2 then 1 else 0 end + 
    case when c = 3 then 1 else 0 end + 
    case when d = 4 then 1 else 0 end + 
    case when e = 5 then 1 else 0 end + 
    case when f = 6 then 1 else 0 end as score
  from t
) x
where score >= 3
order by score desc

    Of course, this query won’t be efficient in terms of execution time, but should work well for small subsets of data.

    Login or Signup to reply.
  2. 0

    In MySql boolean expressions are evaluated as 1 for true or 0 for false, so you can add them in the WHERE clause:

    WHERE (A = 1) + (B = 2) + (C = 3) + (D = 4) + (E = 5) + (F = 6) >= 3

    Just in case any of the 6 columns is nullable, use the NULL-safe equal to operator <=> instead of =:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search