Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2013-01-14
Medium Priority
Last Modified: 2013-01-14
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...

    A = 1
    B = 2
    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"

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


Question by:willsherwood
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 41

Assisted Solution

ralmada earned 1332 total points
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
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 668 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
LVL 41

Accepted Solution

ralmada earned 1332 total points
ID: 38775416
mysql also supports if()


where       if(A=1, 1, 0) +
      if(B=2, 1, 0) +
      if(C=3, 1, 0)  >= 2
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


Author Closing Comment

ID: 38775423
excellent, many thanks.
this is easier than i had feared.
LVL 48

Expert Comment

by:Dale Fye
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

Author Comment

ID: 38775581
clever,  THANK YOU!
LVL 48

Expert Comment

by:Dale Fye
ID: 38775679
glad to help

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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.
How to create a custom search shortcut to site-search Experts Exchange using Google in the Firefox browser. This eliminates the need to type out site:experts-exchange.com whenever you want to search the site. Launch your Bookmark Menu: Press 'Ctrl +…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question