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!
willsherwoodAsked:
Who is Participating?
 
ralmadaCommented:
mysql also supports if()

so

where       if(A=1, 1, 0) +
      if(B=2, 1, 0) +
      if(C=3, 1, 0)  >= 2
0
 
ralmadaCommented:
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
 
Dale FyeCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
willsherwoodAuthor Commented:
excellent, many thanks.
this is easier than i had feared.
appreciated
0
 
Dale FyeCommented:
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
 
willsherwoodAuthor Commented:
clever,  THANK YOU!
0
 
Dale FyeCommented:
glad to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.