CODEWITHSUNDEEP
t-sql
theteague
theteague

Reputation: 413

Filtering Results across multiple columns

I am running a query where the results look something like this:

          JOHN   JILL   GARY   JANE   JEFF   MICK 
RED     | NOPE |  OK  |  OK  |  OK  |  OK  |  OK  |
ORANGE  |  OK  |  OK  |  OK  |  OK  |  OK  |  OK  |
YELLOW  |  OK  |  OK  | NOPE |  OK  | NOPE |  OK  |
GREEN   |  OK  |  OK  |  OK  |  OK  |  OK  |  OK  |
BLUE    |  OK  |  OK  |  OK  |  OK  |  OK  |  OK  |
INDIGO  |  OK  | NOPE |  OK  |  OK  |  OK  |  OK  |
VIOLET  |  OK  |  OK  |  OK  |  OK  |  OK  | NOPE |

I want to filter out the records where all columns are OK. I only want to see results where one or more of the columns contain a NOPE result.

I have tried lots of different variations where this <> to that or that = this and I give up, I need help.

Upvotes: 0

Views: 47

Answers (3)

David
David

Reputation: 3763

assuming that the number of columns is constant then a where clause looking for where all are OK with a not flip should do it.

WHERE (NOT ((JOHN = 'OK') AND (JILL = 'OK') AND (GARY = 'OK') AND (JANE = 'OK') AND (JEFF= 'OK') AND (MICK = 'OK')))

Upvotes: 1

theteague
theteague

Reputation: 413

WHERE JOHN+JILL+GARY+JANE+JEFF+MICK <> 'okokokokokok'

Upvotes: 2

Alan Burstein
Alan Burstein

Reputation: 7918

Perhaps a WHERE clause that looks like this: 

WHERE JOHN = 'NOPE'
  OR JILL = 'NOPE'
  OR GARY = 'NOPE'
  OR JANE = 'NOPE'
  OR JEFF = 'NOPE'
  OR MICK = 'NOPE'

Upvotes: 0

Related Questions