Post reply

Need some help with Query

  • August 11, 2011 at 8:38 am

    #241612

    Got following data (Sample table)

    Type Value

    1 A

    2 B

    3 Null

    4 B

    5 A

    I would like to have query show all records expect the ones with Value "B"

    I've tried followoing

    select Type, Value from <tablename>

    where value not in ('B')

    select type, value from <tablename>

    where value not like 'B'

    In both cases it only shows the records with value "A", however I also need to see the ones with value "NULL", how can I do this?

    thx.

  • August 11, 2011 at 8:45 am

    #1367343

    what you are seeing is how NULss are treated;

    they are undefined, so you cannot compare them;

    you need to handle the NULL values logiclaly, like with an isnull

    or in your WHERe statement:

    CREATE TABLE #SampleTable (

    TheType INT,

    VALUE VARCHAR(30) )

    INSERT INTO #SampleTable

    SELECT 1 ,'A' UNION ALL

    SELECT 2 ,'B' UNION ALL

    SELECT 3 ,NULL UNION ALL

    SELECT 4 ,'B' UNION ALL

    SELECT 5 ,'A'

    SELECT * FROM #SampleTable WHERE VALUE NOT IN('B')

    SELECT * FROM #SampleTable WHERE ISNULL(VALUE,'') NOT IN('B')

    SELECT * FROM #SampleTable WHERE VALUE NOT IN('B') OR VALUE IS NULL

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 11, 2011 at 9:00 am

    #1367359

    Lowell,

    thx for the advice, but still have issue with this.

    SELECT * FROM #SampleTable WHERE ISNULL(VALUE,'') NOT IN('B')

    ==> That one did work and provided me the results as expected.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply