Reputation: 77
I have a output data as shown below
query used : select * from table 1
table 1
name a b
pen 0 50
paper 10 0
bike 0 0
candy 0 0
disk 20 30
the output should be
name a b
pen 0 50
paper 10 0
disk 20 30
should eliminate the record with both a = 0 and b = 0
If I use the filter condition as
select *
from table1
where a!=0 and b!=0
then I am getting only disk
Upvotes: 0
Views: 1268
Reputation: 262860
You want
WHERE a != 0 OR b != 0 -- get records with at least one of [a, b]
If you happen to have NULL values in those columns they will be excluded just like 0 would (because NULL != 0
evaluates to false).
Upvotes: 3
Reputation: 1565
Try this one:
SELECT
*
FROM
table1
WHERE
NOT (coalesce(a, 0) = 0 AND coalesce(b, 0) = 0)
Upvotes: 0
Reputation: 1689
The reason you are getting only disk
with your where-clause is the AND
you used. AND
means that both conditions must be true, so column a must not be 0 and column b must not 0.
Only when this condition is met, a result is shown. In your example only the disk
dataset meets the condition.
To achieve what you want use an OR
instead.
In order to filter NULL
values you can check for IS NULL
like this:
WHERE (a <> 0 AND a IS NOT NULL) OR (b <> 0 AND b IS NOT NULL)
Upvotes: 0