Vigen  Cholakyans Vigen Cholakyans -4 years ago 113
MySQL Question

Column at the same time can't be equal a certain values (mysql)

I have this table:

+------+----------+-------------+
| id | category | category_id |
+------+----------+-------------+
| 1 | 2 | 12 |
+------+----------+-------------+
| 2 | 1 | 12 |
+------+----------+-------------+
| 3 | 3 | 54 |
+------+----------+-------------+
| 4 | 3 | 17 |
+------+----------+-------------+
| 5 | 2 | 14 |
+------+----------+-------------+


I nead to get the following:

when category = 2 : category_id != 12

i.e

+------+----------+-------------+
| id | category | category_id |
+------+----------+-------------+
| 2 | 1 | 12 |
+------+----------+-------------+
| 3 | 3 | 54 |
+------+----------+-------------+
| 4 | 3 | 17 |
+------+----------+-------------+
| 5 | 2 | 14 |
+------+----------+-------------+


This code does not work:

SELECT * FROM `table` WHERE `category`!=2 AND `category_id`!=12

Answer Source

I think you want OR:

select *
from `table`
where `category` != 2
    or `category_id` != 12

Which, as per De Morgan's law, is same as:

select *
from `table`
where not (
        `category` = 2
        and `category_id` = 12
        )

which reads - get all rows except when category is 2 and category_id is 12 in one row

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download