Adam Adam - 3 months ago 9
MySQL Question

MYSQL: check if one row of result has specific value

I am using a select statement like this

SELECT color, form FROM userTable WHERE type = 42


and get as a result


color | form |
--------------------
red | circle |
blue | rectangle |
blue | circle |



However I would like to get only a single row which is 1 in column color if there exits a row of my result with blue color, otherwise 0, and which is 1 in column form if there exists a row of my result with a line form. So from the above example the result should look like


color | form |
-------------------
1 | 0 |



Is it possible to achieve this with an MySQL command?

Here is what I tried:

SELECT IF(color='blue', 1, 0) as color, IF(form='line', 1, 0) as form
FROM userTable WHERE type = 27 GROUP BY type


but I don't think this works since some of the rows fulfill the IF-conditions and others do not. I would need some statement which is like
IF THERE EXITS A ROW WITH COLOR = 'blue'
or something.

Answer

You have to do it like this:

SELECT IF(COUNT(CASE WHEN color = 'blue' THEN 1 END) > 0, 1, 0) AS color,
       IF(COUNT(CASE WHEN color = 'line' THEN 1 END) > 0, 1, 0) AS form
FROM mytable
WHERE type = 42
GROUP BY type

Demo here