Where clause that returns values that have meet at least two of three criteria

Question

I am trying to write a where clause that will find people who have meet at least two of three criteria. This is an example

   SELECT *
   FROM Personal
   WHERE
   [State] = 'Tx' or [City] = 'Austin' or [Gender] = 'Male'

So It should return anyone who Lives in Texas and Austin or Lives in Texas and is Male and so on, but not someone who just lives in Texas, they have to meet at least two of the criteria

My real query can have more criteria and also include a greater than two or exactly two and so on.

Thanks in advance

Answer 1

You might add matches in a series of case ... then 1 else 0 end statements and compare final result to number of required matches:

   SELECT *
   FROM Personal
   WHERE
     case when [State] = 'Tx' then 1 else 0 end
     + case when [City] = 'Austin' then 1 else 0 end
     + case when [Gender] = 'Male' then 1 else 0 end
     >= 2

Alternatively, you might break it into a list of union all:

SELECT *
FROM   personal
       INNER JOIN (SELECT id
                   FROM   (SELECT id
                           FROM   personal
                           WHERE  state = 'Tx'
                           UNION ALL
                           SELECT id
                           FROM   personal
                           WHERE  city = 'Austin'
                           UNION ALL
                           SELECT id
                           FROM   personal
                           WHERE  gender = 'Male') a
                   GROUP  BY id
                   HAVING COUNT (*) >= 2) a
         ON personal.id = a.id