Clever Geek Handbook

Where is a clause that returns values ​​that meet at least two of the three criteria

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

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

So he must return everyone who lives in Texas and Austin or lives in Texas and is a man, and so on, but not someone who just lives in Texas, they must meet at least two criteria.

My real query might have more criteria and also include more than two or exactly two, etc.

Thank you in advance

+3


source to share


2 answers


You can add matches to the case ... then 1 else 0 row of final statements and compare the final result with the number of matches required:

   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 can split it into a union all list:

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
+4


source 


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


+1


source






More articles:


All Articles