mysql (5.1) > select * from database.table where not foo (on all fields)

Question

I want to select everything in a table where the cell's value is not foo. I thought it would be something similar to WHERE NOT NULL, like SELECT * FROM database.table WHERE NOT 'foo';, but that just returned the column headers with no data.

edit: i want to do this without specifying a particular column (NOT 'foo' for all fields).

Solution 1

As @Jim Garrison answers, you must specify the columns to compare your 'foo' value against. Otherwise you're testing a constant value itself, and that can be either false (zero) or true (nonzero).

SELECT * FROM database.table WHERE NOT 'foo' returns no data because 'foo' is a non-false constant value. Therefore NOT 'foo' is false for every row in the table, and so no rows match and the result is an empty set.

Jim gives one syntax for testing each column in turn. Here's another alternative syntax:

SELECT * FROM database.table WHERE 'foo' NOT IN (column1, column2, column3, ...)

However, I agree with the comment from @Mosty Mostacho. In a relational database, it's weird to test for a single value over many columns. Each column should be a different logical type of attribute, so it's uncommon to look for a similar value among many columns (not impossible, but uncommon).

It usually means you're using repeating groups of columns, which violates First Normal Form. Instead, you may need to create a child table, so that all the values you are searching are in a single column, over multiple rows.

Solution 2

There's no way to do this without specifying the fields and conditions, as in

select * from table where
    col1 != value and
    col2 != value and
    ...

Depending on what you mean by

the cell's value is not foo

you may need to change and (none of the columns in a row match the condition) to or (at least one column does not match the condition).

Comments

[Mark Byers]

You might want to look at dynamic SQL.

[Mosty Mostacho]

This is a weird question. Are you sure you really need to do that?

[Jakob Jingleheimer]

quite sure. i'm kind of surprised it's not a more common thing…

[Borealid]

The fact that you have this problem is indicative of an issue with your database structure. You describe in one of your columns that you want to differentiate between "not answered" questions (NULL) and "answered, not applicable" questions ("foo"). The right way to do this would be to have a table where a row exists iff the user answered the question. A row containing NULL would be "answered N/A" and an omitted row would be "not answered". Do not create a table with one column per answer. Instead, have a questions table, an answers table, and a mapping table.

[Jakob Jingleheimer]

Hi Borealid. There isn't an "answered, not applicable". It's "NOT answered, not applicable". Currently I have a table with Questions as columns, rows as Responses. So I think your suggestion might not apply now?

[Jakob Jingleheimer]

Hi Bill, the table is storing data from a survey that I don't control. In the survey there are numerous "skips" where a question is left invisible, so the field in the Db would otherwise be blank, but when the survey is submitted, the invisible question's value is set to foo. This lets me know that the data for this field should be ignored. So I want to get all data that is not foo.

[Jakob Jingleheimer]

Hi Jim, thank you, but doing it this will end up being quite cumbersome and wildly inefficient. AND would be the appropriate operator as I want to avoid any instance of foo).

[Jakob Jingleheimer]

btw, foo would be recorded as NULL except that i need to differentiate between data that is intentionally blank and data that happens to be blank: say if a user stops taking the survey before reaching the end. the initial INSERT causes fields not on page 1 to be set to NULL. Subsequent UPDATEs from the user progressing thru survey pages change NULL to actually data. But sometimes a question is not applicable (based on responses from previous questions), so the question is kept hidden. I don't want to figure out which is the case post-facto, so hidden questions are recorded as foo

[ypercubeᵀᴹ]

Which of the two answers you got is not sophisticated enough?