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?
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 =: