CODEWITHSUNDEEP
phpmysqlselect
Belgin Fish
Belgin Fish

Reputation: 19847

Select from a table where fields don't match conditions

I'm just wondering what kind of SQL command I could execute that would select all items from a certain table where column A is not equal to x and column B is not equal to x

Something like:

select something from table
where columna does not equal x and columnb does not equal x

Any ideas?

Upvotes: 45

Views: 173502

Answers (7)

Wadih M.
Wadih M.

Reputation: 13462

Above answers will not work if there's a null value.

Here's a shorthand to evaluate fields that can be null using coalesce:

select * from table where coalesce(my_nullable_field, 0) <> 1

Here we tell the query to treat NULL as a 0, without adding a completely other condition for nulls, which would keep wiring simple and the code easily readable.

Upvotes: 0

hazari zulkefli
hazari zulkefli

Reputation: 31

Or can also insert the statement inside bracket.

SELECT * FROM tablename WHERE NOT (columnA = 'x')

Upvotes: 3

Abdul Saleem
Abdul Saleem

Reputation: 10622

You can use like

NOT columnA = 'x'

Or

columnA != 'x'

Or

columnA <> 'x'

And like Jeffly Bake's query, for including null values, you don't have to write like

(NOT columnA = 'x' OR columnA IS NULL)

You can make it simple by

Not columnA <=> 'x'

<=> is the Null Safe equal to Operator, which includes results from even null values.

Upvotes: 8

Prashant Tapase
Prashant Tapase

Reputation: 2147

$sqlquery = "SELECT field1, field2 FROM table WHERE columnA <> 'x' AND columbB <> 'y'";

I'd suggest using the diamond operator (<>) in favor of != as the first one is valid SQL and the second one is a MySQL addition.

Upvotes: 2

KhabaNganba
KhabaNganba

Reputation: 1

You can use also 

select * from tablename where column1 ='a' and column2!='b';

            

Upvotes: -1

Affan
Affan

Reputation: 1140

select * from table where fiels1 NOT LIKE 'x' AND field2 NOT LIKE 'y'

//this work in case insensitive manner

Upvotes: 2

Jeffrey Blake
Jeffrey Blake

Reputation: 9709

The key is the sql query, which you will set up as a string:

$sqlquery = "SELECT field1, field2 FROM table WHERE NOT columnA = 'x' AND NOT columbB = 'y'";

Note that there are a lot of ways to specify NOT. Another one that works just as well is:

$sqlquery = "SELECT field1, field2 FROM table WHERE columnA != 'x' AND columbB != 'y'";

Here is a full example of how to use it:

$link = mysql_connect($dbHost,$dbUser,$dbPass) or die("Unable to connect to database");
mysql_select_db("$dbName") or die("Unable to select database $dbName");
$sqlquery = "SELECT field1, field2 FROM table WHERE NOT columnA = 'x' AND NOT columbB = 'y'";
$result=mysql_query($sqlquery);

while ($row = mysql_fetch_assoc($result) {
//do stuff
}

You can do whatever you would like within the above while loop. Access each field of the table as an element of the $row array which means that $row['field1'] will give you the value for field1 on the current row, and $row['field2'] will give you the value for field2.

Note that if the column(s) could have NULL values, those will not be found using either of the above syntaxes. You will need to add clauses to include NULL values:

$sqlquery = "SELECT field1, field2 FROM table WHERE (NOT columnA = 'x' OR columnA IS NULL) AND (NOT columbB = 'y' OR columnB IS NULL)";

Upvotes: 70

Related Questions