CODEWITHSUNDEEP
sql
Mike Croteau
Mike Croteau

Reputation: 1132

SQL : How do I construct a select query with conditional where clauses?

I would like to perform an SQL select query with conditional 'and' clauses but am having difficulty.

I would like to do something like this:

select * from customer
    where active = true
    and 
    case 
    when state = 'AK'
        then zipcode like '%701'
    when state = 'NV'
        then zipcode like '%523'
    else
        then phone not null;

How do I go about structuring my query to do this?

Thanks in advance for any guidance provided.

Upvotes: 0

Views: 41

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

I think you can use or instead of case.

select * from customer
where active = true
and ( (state = 'AK' and zipcode like '%701') or (state = 'NV' and zipcode like '%523') )
and phone is not null;

Upvotes: 1

Shannon Severance
Shannon Severance

Reputation: 18410

Do you mean?

select * from customer
where active = true
and ((state = 'AK' and zipcode like '%701')
    or (state = 'NV' and zipcode like '%523')
    or (phone not null))

Upvotes: 1

Related Questions