Need some help with Query

• denis.gendera

  Mr or Mrs. 500

  Points: 593

  More actions

  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.

• Lowell

  SSC Guru

  Points: 323482

  More actions

  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!

• denis.gendera

  Mr or Mrs. 500

  Points: 593

  More actions

  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.