Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

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
0
paragod10
Asked:
paragod10
1 Solution
 
paeloCommented:
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
 
paragod10Author 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
 
sujit_kumarCommented:
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
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.

 
JankovskyCommented:
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
 
JankovskyCommented:
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
 
jrb1Commented:
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
 
paragod10Author 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now