MySQL query to return TRUE for rows having positive value?
To return TRUE for positive values and FALSE for negative, use MySQL IF(). Let us first create a table −
mysql> create table DemoTable2038
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Value int
-> );
Query OK, 0 rows affected (0.87 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable2038(Value) values(57);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable2038(Value) values(-100);;
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable2038(Value) values(-78);
Query OK, 1 row affected (0.42 sec)
mysql> insert into DemoTable2038(Value) values(78);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable2038(Value) values(91);
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable2038(Value) values(-34);
Query OK, 1 row affected (0.14 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable2038;
This will produce the following output −
+----+-------+
| Id | Value |
+----+-------+
| 1 | 57 |
| 2 | -100 |
| 3 | -78 |
| 4 | 78 |
| 5 | 91 |
| 6 | -34 |
+----+-------+
6 rows in set (0.00 sec)
Here is the query to return true for rows that have positive value −
mysql> select *,if(Value > 0,true,false) as Result from DemoTable2038;
This will produce the following output −
+----+-------+--------+
| Id | Value | Result |
+----+-------+--------+
| 1 | 57 | 1 |
| 2 | -100 | 0 |
| 3 | -78 | 0 |
| 4 | 78 | 1 |
| 5 | 91 | 1 |
| 6 | -34 | 0 |
+----+-------+--------+
6 rows in set (0.00 sec)
Published on 07-Apr-2020 11:46:19
