Retrieving MySQL records based on a variable set of points of comparison.

Question

Let's say I have a MySQL table, people. Each record comprises a variety of properties, among these favourite_colour, country, and age_group.

What I would like to do is retrieve records from this table by their similarity to a set of specific parameters. Given "Red", "United States", and "18-25", for example, the best results would be those records that match all three. These would be 100% matches.

However, I would also like to retrieve records that match any combination of two parameters (66% match), or any one parameter (33% match). Moreover, I would like to be able to define additional points of comparison (e.g. underwear_type, marital_status, etc.).

Is there a relatively efficient solution to this problem?

Answer 1

For the three first is easy:

select * from people
where
(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)>=33

I don't understand the "additional points of comparison" part, can you explain?