CODEWITHSUNDEEP
mysqlsql
Vincent Omondi
Vincent Omondi

Reputation: 25

SQL STATEMENT WITH WHERE != TWICE

When I run the SQL first sql statement I get results. When i run the Second statement I get no result.

STATEMENT1:

SELECT * FROM table1 INNER JOIN table2 ON table.id = table2.id
WHERE file_id = '5871' AND table1.debit = 0 AND table1.credit = 0

STATEMENT2:

SELECT * FROM table1 INNER JOIN table2 ON table.id = table2.id
WHERE file_id = '5871' AND table1.debit != 0 AND table1.credit != 0

I have tried running sql statement with only one != and it works.

STATEMENT1:

SELECT * FROM table1 INNER JOIN table2 ON table.id = table2.id
WHERE file_id = '5871' AND table1.debit = 0 AND table1.credit = 0

STATEMENT2:

SELECT * FROM table1 INNER JOIN table2 ON table.id = table2.id
WHERE file_id = '5871' AND table1.debit != 0 AND table1.credit != 0

I expect all rows that have 0 debits and credits not to be displayed. This is my db data WHERE file_id = '5871' My database where file id = 5871

Upvotes: 0

Views: 563

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You probably want or:

SELECT *
FROM table1 INNER JOIN 
     table2
     ON table.id = table2.id
WHERE file_id = '5871' AND
      (table1.debit <> 0 OR table1.credit <> 0);

Equivalent logic would be:

SELECT *
FROM table1 INNER JOIN 
     table2
     ON table.id = table2.id
WHERE file_id = '5871' AND
      NOT (table1.debit = 0 AND table1.credit = 0);

If you have no NULL values, both of these will return the complement of the first query.

Upvotes: 2

Hillygoose
Hillygoose

Reputation: 195

On that picture you wont get any data through as the table you have shown wont satisfy BOTH conditions. There isnt a row that has both column 1 not equaling 0 and column 2 not equaling 0, you need to use the operator "or" Whereas you do have some rows where both columns are 0

Upvotes: 3

Related Questions