If or Case in the where statement?

Posted on 2010-01-06
Last Modified: 2012-05-08
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

Question by:directxBOB
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    select * from items where user = 'xxxxx'  and type = 'NotSet'
    LVL 67

    Expert Comment

    select * from items where user = 'xxxxx' and type = 'Set'
    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')

    LVL 6

    Accepted Solution

    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
    LVL 14

    Assisted Solution

    update items set type = 'NotSet'


    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    Title # Comments Views Activity
    index rebuild failed 8 76
    Reserved Blank Space in SQL delimited query 5 16
    SQL help 5 41
    Access Webapp: Table to Query to Table 14 35
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now