CODEWITHSUNDEEP
mysqlsql
artask
artask

Reputation: 439

How can I select all rows where every column doesn't match a value?

I need to select a value itself that is not equals some statement.

Something like

SELECT * FROM table WHERE * != "qwerty"

But not like

SELECT * FROM table WHERE column_name != "qwerty"

How can I do that?

I have a table like

       1   2   3   4   5   6   7   8   9   10   11   ...   ...
    1  a   b   c   d   t   h   v   h   d   t    y    ...   ...
    2  g   t   5   s   h   r   q   q   q   q    q    ...   ...
   ... ...
   ... ...

I need to select every value that is not equals "q"

I could do smth like 

SELECT * WHERE 1 != q AND 2 != q AND 3 != q ...

but I have toomany columns

Upvotes: 3

Views: 1722

Answers (3)

Michael Buen
Michael Buen

Reputation: 39393

Try this:

SELECT * FROM table WHERE "qwerty" NOT IN (column1,column2,column3,column4,etc)

Another example:

-- this...
SELECT 'HELLO!' FROM tblx 
WHERE 'JOHN' NOT IN (col1,col2,col3);

-- ...is semantically equivalent to:
SELECT 'HELLO!' FROM tblx 
WHERE 'JOHN' <> col1
  AND 'JOHN' <> col2
  AND 'JOHN' <> col3;

Data source:

create table tblx(col1 text,col2 text,col3 text);
 insert into tblx values
('GEORGE','PAUL','RINGO'), 
('GEORGE','JOHN','RINGO');

If you are using Postgresql, you can make a shortcut for columns:

select * 
from
(
select 

   row(tblx.*)::text AS colsAsText,

   translate(row(tblx.*)::text,'()','{}')::text[]
      as colsAsArray

from tblx
) x
where 'JOHN' <> ALL(colsAsArray)

Live test: http://www.sqlfiddle.com/#!1/8de35/2

Postgres can make rows from arrays, 'JOHN' <> ALL is equivalent to::

where 'JOHN' NOT IN (SELECT unnest(colsAsArray))

Live test: http://www.sqlfiddle.com/#!1/8de35/6

If the above is really what you wanted to achieve, searching is much better if you use full-text search

For MySQL:

select 
  @columns := group_concat(column_name)
from information_schema.columns
where table_name = 'tblx'
group by table_name;




set @dynStmt := 
   concat('select * from tblx where ? NOT IN (',  @columns ,')');



select @dynStmt;

prepare stmt from @dynStmt;

set @filter := 'JOHN';

execute stmt using @filter;

deallocate prepare stmt;

Live test: http://www.sqlfiddle.com/#!2/8de35/49

Upvotes: 7

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

This will give you the where expression you want.

select GROUP_CONCAT(COLUMN_NAME SEPARATOR ' != ''q'' AND ') as Exp
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'YourTable'

Perhaps you can use this in some dynamic SQL or copy and past the string to your real query.

Upvotes: 3

Songo
Songo

Reputation: 5726

Maybe you can try SHOW COLUMNS:

SHOW COLUMNS FROM SomeTable

This will return all the columns info.

example:

    [Field] => id
    [Type] => int(7)
    [Null] =>  
    [Key] => PRI
    [Default] =>
    [Extra] => auto_increment

You can then use Michael Buen's answer to get the values you want:

SELECT * FROM table WHERE "qwerty" NOT IN (columnName1,columnName2,columnName3,columnName4,etc)

Upvotes: 0

Related Questions