CODEWITHSUNDEEP
mysqlsqlmariadb
tomtom
tomtom

Reputation: 614

Sql statement condition in where Clause

I have a problem in creating a where statement condition for below sql query.

I Wish to add a where condition not to display num1 and num2 value if both of them equals to zero or even any one of them equal to zero as well

SELECT Num1, Num2
FROM Position
WHERE  (Num1 IS NOT NULL AND Num2 IS > NOT NULL)
ORDER BY PositionDateTime DESC ;

Result:

    +---------+---------+
    | Num1    | Num2    |
    +---------+---------+
    |
    | 47.3941 | 8.68005 |
    |       0 |       0 |
    | 47.4385 | 8.61106 |
    | 47.4385 | 8.61106 |

Upvotes: 2

Views: 172

Answers (7)

Nikita Shisodia
Nikita Shisodia

Reputation: 11

SELECT Num1, Num2 
FROM Position 
WHERE (Num1 is not null and Num2 is not null)
ORDER BY PositionDateTime DESC

Upvotes: 0

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following using IFNULL:

SELECT Num1, Num2 
FROM Position 
WHERE IFNULL(Num1, 0) <> 0 AND IFNULL(Num2, 0) <> 0
ORDER BY PositionDateTime DESC

demo: http://sqlfiddle.com/#!9/3a441a/1/0

Explanation: You don't want to show a row if both values (Num1 and Num2) are 0 (or NULL) or if one of these values (Num1 or Num2) is 0 (or NULL). So you only want to show rows where both values (Num1 and Num2) have a value unequal 0 (and NULL). So you can use Num1 <> 0 AND Num2 <> 0 as condition. To set a default value for NULL you can use IFNULL (the second parameter is the value which will be used instead of NULL). In MySQL there should not be a difference between IFNULL(Num1, 0) <> 0 AND IFNULL(Num2, 0) <> 0 and Num1 <> 0 AND Num2 <> 0

extended demo: http://sqlfiddle.com/#!9/11f2a5/1/1

Upvotes: 0

user9093250
user9093250

Reputation: 1

Try : Where Num1<>0 and Num2<>0

Upvotes: 0

Pham X. Bach
Pham X. Bach

Reputation: 5442

You could simply change your query to:

SELECT num1, num2
FROM position
WHERE num1 <> 0 AND num2 <> 0
ORDER BY positiondatetime DESC ;

Upvotes: 0

Damini Suthar
Damini Suthar

Reputation: 1492

    SELECT Num1, Num2 
    FROM Position WHERE Num1 <> 0 AND Num1 IS NOT NULL AND Num1 <> ''
    AND Num2 <> 0 AND Num2 IS NOT NULL AND Num2 <> ''
    ORDER BY PositionDateTime DESC ;

Upvotes: -1

XING
XING

Reputation: 9886

Try this:

 SELECT Num1, Num2 
    FROM Position 
    WHERE Num1 <> 0  
    OR Num2 <> 0
    ORDER BY PositionDateTime DESC ;

Upvotes: 5

Mureinik
Mureinik

Reputation: 311478

You could use a straight forward <> operator:

SELECT   Num1, Num2 
FROM     Position
WHERE    Num1 IS NOT NULL AND Num1 <> 0 AND
         Num2 IS NOT NULL AND Num2 <> 0
ORDER BY PositionDateTime DESC

You could also use coalesce to make the query a tad more elegant, although this is probably a question of personal taste:

SELECT   Num1, Num2 
FROM     Position
WHERE    COALESCE(Num1, 0) <> 0 AND
         COALESCE(Num2. 0) <> 0
ORDER BY PositionDateTime DESC

Upvotes: 0

Related Questions