why mysql show wrong output?

Question

Mysql Query

SELECT * FROM `jobc_labor` WHERE type='Workshop' AND  RO_no='4' AND (status!='Job Not Done' OR status!='Jobclose')

Result

Also tried

SELECT * FROM `jobc_labor` WHERE type='Workshop' AND  RO_no='4' GROUP BY `RO_no` HAVING status!='Job Not Done' OR status!='Jobclose'

AND tried with <> but same result

SELECT * FROM `jobc_labor` WHERE type='Workshop' AND  RO_no='4' AND (status<>'Job Not Done' OR status<>'Jobclose')

My question is why mysql giving my output of the rows which has status of 'job Not Done and Jobclose??? What I am doing wrong. I want to write the query which check wether all labor type with workshop has jobclose or job not done OR they are empty which means the work is in process. please help.

Go through the answers here: https://stackoverflow.com/questions/5658457/not-equal-operator-on-null — Kaushik Nayak, Oct 19 '17 at 04:46
The first and last statements are the same (and the condition should be = ) , the second is inappropriate because there is a group by without any aggregations. Define empty do you mean null or empty string - they are not the same thing. — P.Salmon, Oct 19 '17 at 06:28

score 1 · Answer 1 · answered Oct 19 '17 at 04:52

Your status condition:

AND (status!='Job Not Done' OR status!='Jobclose')

is always true because when status is equal 'Job Not Done' then status is not equal to 'Jobclose' and vice-versa.

You can change it to:

AND status!='Job Not Done' 
AND status!='Jobclose'

or use NOT IN:

AND status NOT IN ('Job Not Done', 'Jobclose')

score 0 · Answer 2 · answered Oct 19 '17 at 04:55

0

Try This query:

SELECT * FROM `jobc_labor` 
WHERE type='Workshop' AND  RO_no='4' AND status not in ('Job Not Done','Jobclose');

When you put or , you need be to more careful.

answered Oct 19 '17 at 04:55

Bhanuchander Udhayakumar

1,581
1
12
30

why mysql show wrong output?

2 Answers2