# mysql: how to formulate WHERE for 2-out-of-3 "matches"

i need to select records where at least 2 of 3 conditions are met.
How is this best done?
Below is probe code for what i'm after...

SELECT * FROM tbl WHERE
A = 1
OR
B = 2
OR
C = 3

if the WHERE clause were an "expression language" then something like the following might suffice  which "adds up"  the successful terms in the "equation"

WHERE  (
(if (A=1) then 1 else 0)
+
(if (B=2) then 1 else 0)
+
(if (C=3) then 1 else 0)

)>=2

thanks!
###### Who is Participating?

Commented:
mysql also supports if()

so

where       if(A=1, 1, 0) +
if(B=2, 1, 0) +
if(C=3, 1, 0)  >= 2
0

Commented:
you could try

where       (case when A=1 then 1 else 0 end) +
(case when B=2 then 1 else 0 end) +
(case when C=3 then 1 else 0 end)  >= 2
0

Commented:
Not sure of the MySQL syntax, but try something like:

SELECT *,  (if(A=1, 1, 0) + if(B=2, 1, 0) + if(C=3, 1, 0)) as Matches
FROM yourTabletbl
WHERE (if(A=1, 1, 0) + if(B=2, 1, 0) + if(C=3, 1, 0)) >= 2
0

Author Commented:
excellent, many thanks.
this is easier than i had feared.
appreciated
0

Commented:
The advantage of including the Matches column in the query results is that you can then identify how many of the matches were made.

Another alternative, if you want to be able to identify which conditions matched by simply looking at the Matches field would be:

SELECT *,  (if(A=1, "A","") & if(B=2, "B", "") & if(C=3, "C", "")) as Matches
FROM yourTabletbl
WHERE LEN(if(A=1, "A","") & if(B=2, "B", "") & if(C=3, "C", "")) >= 2
0

Author Commented:
clever,  THANK YOU!
0

Commented: