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

Question

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 1

This should do it

SELECT id, a, b, IF(a<b, 1,0) as cond
FROM t
WHERE a<b OR b+1=a;

Sign up