willsherwood
asked on
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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
clever, THANK YOU!
glad to help
ASKER
this is easier than i had feared.
appreciated