TSQL ignoring multiple AND clauses

• kate osborn

  Grasshopper

  Points: 15

  More actions

  February 27, 2014 at 7:13 am

  #75688

  I am running a query that where an input parameter is set to "N" it should ignore rows where column a is "x" and column b is "-99"

  The query I am trying is:

  DECLARE @IncludeUnavailableBookings VARCHAR(1)

  SET @IncludeUnavailableBookings = 'N'

  SELECT

  *

  FROM

  Table1 A

  LEFT JOIN Table2 B ON B.ID = A.ID

  WHERE

  (

  (@IncludeUnavailableBookings = 'Y') OR

  (@IncludeUnavailableBookings = 'N' AND (A.Col1 <> 'X' AND A.Col2<> '-99'))

  )

  However this excludes all rows where EITHER Col1 = 'X' OR A.Col2= '-99'

  What I want to do is only exclude rows where both conditions are met. I can get around this by concatenating the two columns as follows:

  WHERE

  (

  (@IncludeUnavailableBookings = 'Y') OR

  (@IncludeUnavailableBookings = 'N' AND A.Col1+CONVERT(VARCHAR(20),A.Col2) <> 'X-99'))

  However can anyone tell me why the first query is not working the same way please?

• Sushil Dwivedi

  SSC Eights!

  Points: 973

  More actions

  February 27, 2014 at 10:40 pm

  #1692943

  if you put OR in place of AND (Col1 <> 'X' or Col2<> '-99'), you will get same result from both the Sql

  create table #temp (id int,col1 varchar(30),col2 varchar(30))

  insert into #temp(id,col1,col2)

  select 1,'A','-99'

  union all

  select 2,'B','10'

  union all

  select 3,'X','-99'

  union all

  select 4,'Z','-99'

  union all

  select 5,'X','-99'

  DECLARE @IncludeUnavailableBookings VARCHAR(1)

  SET @IncludeUnavailableBookings = 'N'

  select * from #temp where @IncludeUnavailableBookings = 'N' AND (Col1 <> 'X' or Col2<> '-99')

  select * from #temp where @IncludeUnavailableBookings = 'N' AND (Col1+CONVERT(VARCHAR(20),Col2) <> 'X-99')

• anastke

  SSChasing Mays

  Points: 632

  More actions

  February 28, 2014 at 6:10 am

  #1693017

  negative logic: ( a & b) <=> (!a | !b)

  you want to select

  WHERE a OR (b AND NOT (c AND d))

  which would be equiv. to

  WHERE a OR (b AND (NOT c OR NOT d))

  so either:

  (@IncludeUnavailableBookings = 'Y') OR

  (@IncludeUnavailableBookings = 'N' AND NOT (A.Col1 = 'X' AND A.Col2 = '-99'))

  or:

  (@IncludeUnavailableBookings = 'Y') OR

  (@IncludeUnavailableBookings = 'N' AND (A.Col1 <> 'X' OR A.Col2 <> '-99'))