Why is "not equal to" not working in SQL Server

SELECT     TOP (100) PERCENT dbo.Job.Job, dbo.Job.Customer, dbo.Job.Rev, dbo.Job.Part_Number, dbo.Job.Description, dbo.Job.Status, dbo.Delivery.Promised_Quantity, 
                      dbo.Job.Order_Unit, dbo.Delivery.Promised_Date, dbo.Delivery.Comment, dbo.Job_Operation.Work_Center, dbo.Job_Operation.Status AS Expr1, 
                      dbo.Delivery.Remaining_Quantity, dbo.Job_Operation.Note_Text, ISNULL(SUBSTRING(dbo.Job_Operation.Note_Text, 11, 15), 'No Date') AS Expr2, 
                      dbo.Job_Operation.Est_Total_Hrs
FROM         dbo.Job INNER JOIN
                      dbo.Job_Operation ON dbo.Job.Job = dbo.Job_Operation.Job INNER JOIN
                      dbo.Delivery ON dbo.Job.Job = dbo.Delivery.Job
WHERE     (dbo.Job.Status = 'active') AND (dbo.Job_Operation.Status = 'o' OR
                      dbo.Job_Operation.Status = 's') AND (dbo.Delivery.Remaining_Quantity > 0) AND (dbo.Job_Operation.Work_Center <> 'DELV-FLATB' OR
                      dbo.Job_Operation.Work_Center <> 'DELV-COMBO' OR
                      dbo.Job_Operation.Work_Center <> 'DELV-PICKU')

Open in new window


Please see attached image.
Capture.JPG
Robert FrancisDirector of Continuous ImprovementAsked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
or use IN instead:
dbo.Job_Operation.Work_Center NOT IN ('DELV-FLATB', 'DELV-COMBO', 'DELV-PICKU')

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You should have "AND", not "OR", i.e.

(dbo.Job_Operation.Work_Center <> 'DELV-FLATB' AND
                      dbo.Job_Operation.Work_Center <> 'DELV-COMBO' AND
                      dbo.Job_Operation.Work_Center <> 'DELV-PICKU')
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
The reason is that the OR evaluates each condition separately, and if ONE (or more) of them is true, says True.

The AND requires that ALL of them are true.

So, if dbo.Job_Operation.Work_Center = 'DELV-FLATB' , then

dbo.Job_Operation.Work_Center <> 'DELV-FLATB' - that's false
dbo.Job_Operation.Work_Center <> 'DELV-COMBO' - that's true
dbo.Job_Operation.Work_Center <> 'DELV-PICKU' - that's true.

Because more than zero were true, OR returns True.
However, because they weren't all true, AND returns False.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
However, because they weren't all true, AND returns False.
That is not correct in regard of the OR subexpression. The ORs are always true, as you correctly stated. The AND is redundant then, because
   x AND TRUE
is always   x.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Which is what I said.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.