MySQL Where clause same key twice for two separate values

Question

I am generating the following query

SELECT * FROM Report WHERE submittedOn >= :minDateFilter 
AND submittedOn <= :maxDateFilter AND non_issue = 1 AND 
non_issue = 0 ORDER BY submitted

However, I'm not getting anything returned (it works fine if I omit: AND non_issue = 1 AND non_issue = 0, or omit just one of these, but nothing is being returned when both of these clauses are present.

Ideally I'd like to keep the statement how it is, as it's generated on the fly and I'd like to be able to display both types of report from the same query.

I feel like my syntax might be incorrect, but it's not throwing any errors, any suggestions?

score 3 · Answer 1 · edited Jul 13 '16 at 14:10

3

non_issue can not have 2 values at same time on a given row. I think you would like to use OR:

 SELECT * FROM Report WHERE submittedOn >= :minDateFilter 
AND submittedOn <= :maxDateFilter AND (non_issue = 1 OR 
non_issue = 0) ORDER BY submitted

edited Jul 13 '16 at 14:10

Bill Karwin

538,548
86
673
828

answered Jul 13 '16 at 14:09

Jens

67,715
15
98
113

better AND non_issue IN (0,1) – AlecTMH Jul 13 '16 at 14:11
@AlecTMH why you think IN is better? – Jens Jul 13 '16 at 14:12
I do not know. Maybe the optimizer handles it the same way?! – Jens Jul 13 '16 at 14:13
It depends of the dataset however the syntax looks better in that case. See http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance for reference. – AlecTMH Jul 13 '16 at 16:16

score 1 · Answer 2 · answered Jul 13 '16 at 14:11

You can use the IN instead of non_issue = 1 AND non_issue = 0 condition.

The same entry can't be non_issue equal to 0 and 1 same time.

SELECT * 
FROM Report 
WHERE submittedOn >= :minDateFilter AND 
      submittedOn <= :maxDateFilter AND 
      non_issue IN (0, 1)
ORDER BY submitted

MySQL Where clause same key twice for two separate values

2 Answers2