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 to share