• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

If or Case in the where statement?

I have two types of items : Set , NotSet

my typical statement would be:

select * from items where user = 'xxxxx'

but I now need to adjust this so that if the user has Set Items and NotSet Items then I would only show the NotSet.

Wasn't exactly sure how I could achieve this

0
directxBOB
Asked:
directxBOB
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
select * from items where user = 'xxxxx'  and type = 'NotSet'
0
 
QlemoC++ DeveloperCommented:
select * from items where user = 'xxxxx' and type = 'Set'
union
select * from items o where user = 'xxxxx' and type = 'NotSet'
    and not exists (select * from items i where o.user = i.user and i.type = 'Set')

0
 
magedroshdyCommented:
SELECT
case when count(distinct type) > 1 then 'NotSet'
else case when count(user) > 1 then type end
  FROM items
 WHERE USER = 'xxxxx'
 group by type, user
0
 
shru_0409Commented:
update items set type = 'NotSet'

or

select * from items where case when type is null or type = 'Set' then 'Set' ELSE 'NotSet' end = case when type is null or type = 'Set' then 'Set' ELSE 'NotSet' end
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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