Solved

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

Posted on 2013-01-14
200 Views
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!
0
Question by:willsherwood
• 3
• 2
• 2

LVL 41

Assisted Solution

ID: 38775405
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

LVL 47

Assisted Solution

Dale Fye (Access MVP) earned 167 total points
ID: 38775411
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

LVL 41

Accepted Solution

ID: 38775416
mysql also supports if()

so

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

Author Closing Comment

ID: 38775423
excellent, many thanks.
this is easier than i had feared.
appreciated
0

LVL 47

Expert Comment

ID: 38775511
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 Comment

ID: 38775581
clever,  THANK YOU!
0

LVL 47

Expert Comment

ID: 38775679
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

If you are a web developer, you would be aware of the <iframe> tag in HTML. The <iframe> stands for inline frame and is used to embed another document within the current HTML document. The embedded document could be even another website.
Several part series to implement Internet Explorer 11 Enterprise Mode
Google currently has a new report that is in beta and coming soon to Webmaster Tool accounts. This Micro Tutorial will highlight new features for Google Webmaster Tools.
This Micro Tutorial will demonstrate how nuggets on the Web are formatted by using Chrome Developer Tools. These tools would not only view the site's CSS but it can also modify it and save the CSS to use on your own site.