# SQL Logic to return records when 2 out of 3 conditions are true.

If X, Y, and Z are conditioinal statements, I want to return records when two of the three are true.  All I've come up with so far is the following:

select * from mytable where ((X AND Y) OR Z) AND (X OR (Y AND Z))

Not sure if it's possible but 500 pts if you can do better (in other words, more elegant)

-P
LVL 3
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
WHERE CASE WHEN X THEN 1 ELSE 0 END + CASE WHEN Y THEN 1 ELSE 0 END + CASE WHEN Z THEN 1 ELSE 0 END = 2

-Paul.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Paul,

Not bad.  You did remind me of a slight inaccuracy in what I am looking for though.  If 2 OR MORE are true I want to return results.  So I'd throw a > in front of your =.

P
0
Commented:
select * from mytable where (X AND Y) OR (Y AND Z) OR (Z AND X);

FOR EXAMPLE,

select * from emp where (ename='Y' AND sal=100) OR (sal=100 AND comm=0) OR (comm=0 AND ename='Y');

Sujit
0
Commented:
some of another possibilities:

select * from mytable where (X AND Y) OR ((X OR Y) AND Z)

select * from mytable where (X OR Y) AND (X OR Z) AND (Y OR Z)

select * from mytable where (X OR Y) AND (X OR (Y AND Z))

0
Commented:
But I think, Paelo's solution (a bit improved):

WHERE CASE WHEN X THEN 1 ELSE 0 END + CASE WHEN Y THEN 1 ELSE 0 END + CASE WHEN Z THEN 1 ELSE 0 END >= 2

is very elegant mostly in the case of complex conditions - you don't need repeat the definition and statement is better manageable during changes.

Bob
0
senior developerCommented:
What database is this for?  This works in Access (and most likely SQL Server):

select *
from mytable
where (x) + (y) + (z) <= -2;

This is because True is evaluated as -1 and false as 0.  So the sum of these three conditions can be 0, -1, -2, or -3.
0
Author Commented:
Paelo's answer was the simplest and most scalable (i.e. I can add more conditions later if need be and not make it any more complicated).  jrb1, I wish your solution worked but it did not on SQL Server.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

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.