Solved

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

Posted on 2013-01-14
7
201 Views
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...

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
Comment
Question by:willsherwood
  • 3
  • 2
  • 2
7 Comments
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 333 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
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
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

by:
ralmada earned 333 total points
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Closing Comment

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

Expert Comment

by:Dale Fye (Access MVP)
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

by:willsherwood
ID: 38775581
clever,  THANK YOU!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38775679
glad to help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
SSL stands for “Secure Sockets Layer” and an SSL certificate is a critical component to keeping your website safe, secured, and compliant. Any ecommerce website must have an SSL certificate to ensure the safe handling of sensitive information like…
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.

792 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