Solved

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

Posted on 2005-03-09
Medium Priority
397 Views
Last Modified: 2006-11-17
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
0
Question by:paragod10
[X]
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
7 Comments

LVL 9

Accepted Solution

paelo earned 2000 total points
ID: 13503057
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

LVL 3

Author Comment

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

LVL 11

Expert Comment

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

LVL 6

Expert Comment

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

LVL 6

Expert Comment

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

LVL 25

Expert Comment

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

LVL 3

Author Comment

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

Featured Post

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our companyâ€™s databases and systems would, ideally, have time to devote to learning the ins and outsâ€¦
In this article, Iâ€™ll look at how you can use a backup to start a secondary instance for MongoDB.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demâ€¦
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastrâ€¦
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

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

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