SQL Query 'Where" clause problem.

Posted on 2012-09-14
Last Modified: 2012-09-17
I need to include only the [Status] entries as detailed in the "where [Status] in
  ('Blacklisted', 'Compatible', 'Packaged', 'Unknown', 'Unmanaged' )" statement.  Unfortunately one of the other "Status" options is "Incompatible" and that value is also being returned when I need to exclude it.  

I have only sub-basic knowledge of SQL and the DBA that wrote this for me is on vacation.  I can only imagine it is because 'compatible' is a word within 'incompatible'.

Can anyone tell me how I can modify the statement below to ONLY return the values as shown while excluding "Incompatible"?

Any assistance would be GREATLY appreciated!

------------ START SQL QUERY--------------------
USE [ALLAppDatabase]

/****** Object:  View [dbo].[vwAppsAndStatusFiltered]    Script Date: 09/14/2012 12:40:05 ******/


/****** Script for SelectTopNRows command from SSMS  ******/
CREATE view [dbo].[vwAppsAndStatusFiltered]
  FROM [ALLAppDatabase].[dbo].[AppsAndStatus]
  where [Status] in
  ('Blacklisted', 'Compatible', 'Packaged', 'Unknown', 'Unmanaged' )
--  Status
--Packaged --
--Blacklisted --
--In Remediation
--Excluded ?


------------ END SQL QUERY--------------------
Question by:tneubauertocg
    LVL 65

    Assisted Solution

    by:Jim Horn
    >because 'compatible' is a word within 'incompatible'.
    This wouldn't be the case as I don't see the LIKE keyword with any wildcards ( % ).

    The WHERE clause as written should limit the rows to what you have in the IN block.

    Some possibilities:
    The table [ALLAppDatabase].[dbo].[AppsAndStatus] refers to something other than what you are expecting.
    The column status refers to something other than what you are expecting.
    LVL 142

    Accepted Solution

    I confirm that IN() will only return values that match exactly.
    I presume you have some other conditions, eventually ORed in your WHERE clause, that result in this behavior

    Author Closing Comment

    After reading your comments I now believe it has to do with some inner joins on some other tables.  My DBA is back today so I will confirm.


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. (htt…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now