SQL hide result if all three fields are 'Yes'

Question

I a have a MySQL db with 4 fields:

id | planchanged | dataremoved | rolloverenabled |
1  | Yes         | Yes         | Yes             |
2  | NULL        |             |                 |
3  |             | Yes         |                 |
4  | Yes         |             | Yes             |
5  |             |             | NULL            |

How do I query this db to show only records that doesn't have all 'Yes' on those three fields. Please also take into consideration that some of thos fields might be Null. So based on that example my result should show records 2,3,4 and 5.

"Please also take into consideration that some of thos fields might be Null" --- looks like a job description. Have you tried to solve your task yourself? — zerkms, Jan 14 '13 at 02:16
yes I did but I can only get the result to show when all three are blank and any Null records are not coming up. Not sure what you mean by job description though. — Renee Cribe, Jan 14 '13 at 02:22
And just for fun - the most straightforward condition that doesn't need to take `NULL` behaviour into account is: `NOT (planchanged = 'Yes' AND dataremoved = 'Yes' AND rolloverenabled = 'Yes')` — zerkms, Jan 14 '13 at 02:31

rs. · Answer 1 · 2013-01-14T02:27:34.720

3

You can do this

SELECT * FROM TableName 
WHERE coalesce(planchanged,'-') <> 'Yes' OR 
coalesce(dataremoved,'-') <> 'Yes' OR
coalesce(rolloverenabled,'-') <>'Yes'

SQL FIDDLE DEMO

edited Jan 14 '13 at 02:27

answered Jan 14 '13 at 02:21

rs.

26,707
12
68
90

If 2 columns are yes, and a 3rd isn't yes he wants it to show – Boundless Jan 14 '13 at 02:23
Just replace `AND` with `OR` – zerkms Jan 14 '13 at 02:24
Be aware that using coalesce in the where clause will stop the use of any indexes on those columns – lc. Jan 14 '13 at 02:33
@lc. Thank you, I was not aware of that. In that case your answer should work for OP. I'll leave my answer as is. – rs. Jan 14 '13 at 02:37

score 3 · Accepted Answer · answered Jan 14 '13 at 02:27

3

SELECT * 
FROM yourTableName 
WHERE planchanged IS NULL OR planchanged <> 'Yes' 
OR dataremoved IS NULL OR dataremoved <> 'Yes' 
OR rolloverenabled IS NULL OR rolloverenabled <> 'Yes'

SQL Fiddle

answered Jan 14 '13 at 02:27

lc.

113,939
20
158
187

Aren't these redundant: planchanged IS NULL OR planchanged <> 'Yes' – Boundless Jan 14 '13 at 02:28
@Boundless: `NULL` never matches to anything. So `NULL = 'Yes'` == false, and `NULL <> 'Yes'` == false – zerkms Jan 14 '13 at 02:29
@Boundless Unfortunately, not for mysql (and most others depending on NULL matching settings). See http://www.sqlfiddle.com/#!2/2fdf4/3 – lc. Jan 14 '13 at 02:29
Thank you for the reply and all the comments I certainly learned some new stuff about NULL – Renee Cribe Jan 14 '13 at 02:40

score 0 · Answer 3 · edited Jan 14 '13 at 02:43

0

SELECT * 
FROM  yourTableName 
WHERE planchanged IS NULL 
  OR  dataremoved IS NULL 
  OR  rolloverenabled IS NULL 
  OR  planchanged <> 'Yes' 
  OR  dataremoved <> 'Yes' 
  OR  rolloverenabled <> 'Yes'

edited Jan 14 '13 at 02:43

Leigh

28,765
10
55
103

answered Jan 14 '13 at 02:20

Boundless

2,444
2
25
40

"..show only records that doesn't have all 'Yes'..." shouldn't those OR's be AND's? – Class Jan 14 '13 at 02:21
no, they shouldn't. The query is correct. Please read the question. – Boundless Jan 14 '13 at 02:22
Great, I added is null check to the query. I didn't know that 'yes' <> NULL was false – Boundless Jan 14 '13 at 02:33
Yep, because `null` is not equal to anything. see http://stackoverflow.com/questions/1833949/why-is-null-not-equal-to-null-false – Leigh Jan 14 '13 at 02:42
It's funny that it's not equal to anything, yet it's not not equal either. – Boundless Jan 14 '13 at 02:46
Yeah, I remember thinking it was strange at first. But when you consider that `null` basically means "we do not know what the value is", there is no way to know what it does (or does *not*) equal. – Leigh Jan 15 '13 at 05:47

SQL hide result if all three fields are 'Yes'

3 Answers3