CODEWITHSUNDEEP
mysqlsqldatabase
Kamran Ahmed
Kamran Ahmed

Reputation: 12428

MySQL : Select all the rows except those having the specified values in columns

I have data in my table that looks like the following.

 dcno | etype | debit | credit |
------+-------+-------+--------+
  1   | sale  | 1200  | 1000   |
  2   | sale  | 1000  | 600    |
  3   | sale  | 1100  | 9001   |
  1   | purc  | 1274  | 1281   |
  4   | sale  | 1119  | 2811   |
  6   | sale  | 2345  | 233    |
  2   | purc  | 4467  | 121    |
  3   | sale  | 8885  | 1214   |

What I want is a query that doesnt consider the row having the specified dcno and etype. For example if I specify that I don't want the row having the dcno of 1 and etype of sale it should return the following:

 dcno | etype | debit | credit |
------+-------+-------+--------+
  2   | sale  | 1000  | 600    |
  3   | sale  | 1100  | 9001   |
  1   | purc  | 1274  | 1281   |
  4   | sale  | 1119  | 2811   |
  6   | sale  | 2345  | 233    |
  2   | purc  | 4467  | 121    |
  3   | sale  | 8885  | 1214   |

I have tried with the following 

SELECT  *
FROM    pledger
WHERE   dcno <> 1 AND pledger.etype <> 'sale'

but that didn't work (as expected). Can anyone please tell me how may I achieve this?

Upvotes: 0

Views: 346

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You want or:

SELECT  *
FROM    pledger
WHERE   dcno <> 1 OR pledger.etype <> 'sale'

Boolean logic says that NOT (X AND Y) is logically equivalent to NOT X OR NOT Y. So, NOT (dcno = 1 AND etype = 'sale') is dcno <> 1 OR etype <> 'sale'. Note: this doesn't take NULL into account, but that is not relevant here.

Here is the SQLFiddle

Upvotes: 3

Related Questions