?
Solved

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

Posted on 2005-03-09
7
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
Comment
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

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

by:paragod10
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

by:sujit_kumar
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 6

Expert Comment

by:Jankovsky
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

by:Jankovsky
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

by:jrb1
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

by:paragod10
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.
Video by: Steve
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…

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.

Join & Ask a Question