0

I need to only show records where ship_status is final AND field1 is not null OR field2 is not null. The ship_status = final is a MUST the field1/field2 is either or, however one must be true. But when I try my query it returns all results?

Create Table #123
(
  ID int,
  ship_status varchar(100),
  field2 varchar(100),
  field3 varchar(100)
)

Insert Into #123 Values
('1','Final', 'No', 'Null'),
('2', 'Test', 'Null', 'Null'),
('3','Null', 'Null', 'Null'),
('4','Null', 'Null', 'Null'),
('5','Final', 'Null', 'No'),
('6','Final', 'Null', 'No'),
('7','Final', 'Null', 'No')

Select * from #123
WHERE field2 IS NOT NULL OR field3 IS NOT NULL
AND ship_status IN ('Final')
Big Pimpin
  • 427
  • 9
  • 23

3 Answers3

1

You need to group your OR logic together (with brackets) like so:

Select * from #123
WHERE (field2 IS NOT NULL OR field3 IS NOT NULL)
      AND ship_status IN ('Final')

Also I'd change the AND part to an equality check if you're just searching for a single status:

ship_status = 'Final'
Tanner
  • 22,205
  • 9
  • 65
  • 83
1

Precisely what @Tanner said. Logical operations in SQL has priority of execution so AND has a higher priority than OR Some more details here: sql-logic-operator-precedence-and-and-or

Community
  • 1
  • 1
Alex Shtafinskiy
  • 31
  • 2
0

First of all check your data, it should be:

Insert Into #123 Values
('1','Final', 'No', Null),
('2', 'Test', Null, Null),
('3','Null', Null, Null),
('4','Null', Null, Null),
('5','Final', Null, 'No'),
('6','Final', Null, 'No'),
('7','Final', Null, 'No')

If so, your query could be:

Select ID, ship_status, field2, field3 from #123
WHERE (field2 IS NOT NULL OR field3 IS NOT NULL)
AND ship_status = 'Final'

Otherwise you have varchar with string value "Null". In that case:

Select ID, ship_status, field2, field3 from #123
WHERE (field2 <> 'NULL' OR field3 <> 'NULL')
AND ship_status = 'Final'
Margus
  • 19,694
  • 14
  • 55
  • 103