phinz phinz - 1 year ago 175
MySQL Question

MYSQL SELECT: How to get an additional column that indicates whether the first or the second condition in a WHERE clause was met?

How can I add a boolean column to a

SELECT
statement with a
WHERE
clause and to conditions combined with an
OR
that is 1 if the first condition was met and 0 if the second and not the first condition was met?

As an example take the following table
t
with integer columns:

+----+---+---+
| id | a | b |
+----+---+---+
| 1 | 2 | 3 |
| 2 | 3 | 2 |
| 3 | 5 | 2 |
+----+---+---+


When I execute the statement
SELECT * FROM t WHERE a<b OR b+1=a;
I get:

+----+---+---+
| id | a | b |
+----+---+---+
| 1 | 2 | 3 |
| 2 | 3 | 2 |
+----+---+---+


And what I want is an additional column, say
cond
, like

+----+---+---+------+
| id | a | b | cond |
+----+---+---+------+
| 1 | 2 | 3 | 1 |
| 2 | 3 | 2 | 0 |
+----+---+---+------+


How can this be achieved in MySQL?

Answer Source

This should do it

SELECT id, a, b, IF(a<b, 1,0) as cond
FROM t
WHERE a<b OR b+1=a; 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download