CODEWITHSUNDEEP
sqlsql-servercasewhere-clause
MathLover
MathLover

Reputation: 75

MIcrosoft SQL Server WHERE/ CASE clauses

I have a where statement that depends on an id and based off the id the next where is determined. EX: if ID = 1 the where statement should be a<= 3 and b between 4 and 7 if ID <> 1 the where statement should be a<= 4 and b between 5 and 7. Not sure how to do this. Tried a Case clause but had no luck.

Upvotes: 0

Views: 97

Answers (3)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

Here is a sample table in tempdb with data.

-- Just a test
use tempdb;
go

-- Drop table
if object_id('test') > 0
drop table test
go

-- Create table
create table test
(
    id int,
    a int,
    b int
);

-- Add data
insert into test values
(1, 3, 4),
(2, 4, 5),
(1, 4, 4),
(2, 5, 5),
(1, 3, 3),
(2, 4, 4);

-- Full table
select * from test;

Here is a solution using the CASE statement.

-- Show the data
select 
    * 
from 
    test
where
  (
    case
        when id = 1 and a <= 3 and b between 4 and 7 then 1
        when id <> 1 and a <= 4 and b between 5 and 7 then 1
        else 0
    end
  ) = 1;

enter image description here

Upvotes: 2

JNK
JNK

Reputation: 65157

Based on your requirements you just need to parenthetical WHERE statements with an OR:

...
WHERE (ID = 1 AND a <= 3 AND b BETWEEN 4 AND 7)
       OR (ID <> 1 AND a<= 4 AND b BETWEEN 5 AND 7)

Upvotes: 0

Jord&#227;o
Jord&#227;o

Reputation: 56477

Something like:

where
  (id = 1 and a <= 3 and b between 4 and 7) or 
  (id <> 1 and a <= 4 and b between 5 and 7)

Upvotes: 1

Related Questions