CODEWITHSUNDEEP
mysql
Devin Rodriguez
Devin Rodriguez

Reputation: 1164

MySQL filter on multiple simultaneous conditions

Say I have the following data in a table named names_and_dates:

---------------------
| name | date       |
| John | 2015-01-01 |
| John | 2015-07-02 |
| Mary | 2015-01-01 |
| Mary | 2015-07-02 |
---------------------

How can I query for any data that is not the name John and does not have the date 2015-01-01 at the same time? Normally I would think of a query like:

SELECT * FROM `names_and_dates` WHERE `name` != 'John' AND `date` != '2015-01-01';

But this instead only returns a single row of Mary on 2015-07-02, when really I want to return 3 rows where both rows do not match at the same time. This leads me to a query like:

SELECT * FROM `names_and_dates` WHERE CONCAT(`name`,`date`) != CONCAT('John','2015-01-01');

Which would return the expected data of:

---------------------
| name | date       |
| John | 2015-07-02 |
| Mary | 2015-01-01 |
| Mary | 2015-07-02 |
---------------------

Is this the right way of going about this or is there a better way?

Upvotes: 2

Views: 174

Answers (1)

Dijkgraaf
Dijkgraaf

Reputation: 11527

If you want to find things not matching multiple conditions, wrap them in brackets and use a NOT

SELECT * FROM `names_and_dates` WHERE NOT (`name` = 'John' AND `date` = '2015-01-01');

Upvotes: 3

Related Questions