Retrieving MySQL records based on a variable set of points of comparison
Yes, you can turn each comparison, such as favourite_colour='Red' &c, into a value of 0 (false) or 1 (true) -- mysql will do it implicitly, but for generality you might wantCAST( (favourite_colour='Red') AS INTEGER) &c; then, you SUM all of these, i.e.,
SELECTuserId,SUM( (favourite_colour='Red'), (country='US'), (age_group='18-25') ) AS match_scoreFROM peopleWHERE match_score >= 2ORDER BY match_score DESCwill give you perfect matches first, 2-of-3 next; easy to generalize to even more checks!-)
For the three first is easy:
select * from peoplewhere(case when color = 'Red' then 33 else 0 end + case when age_group = '18-25' then 33 else 0 end + case when country = 'United States' then 33 else 0 end)>=33I don't understand the "additional points of comparison" part, can you explain?