Scott Theriault Scott Theriault - 12 days ago 8
MySQL Question

Is it posible to run CASE WHEN with pre queryed data

Just playing around and I was wondering if it is possible to get the count of a query, plus check if a entity within the query exists at the same time.

So I was looking to do something like this

SELECT
COUNT(a.*)
CASE WHEN ? IN (a.column) THEN 1 ELSE 0 END AS exist
FROM a
WHERE ...


I know I could do a sub-query in the CASE, but is it possible to do it with just the data from the initial query?

Thanks in advance.




EDIT

ie

+------------+
| id column |
+------------+
| 1 5 |
| 2 6 |
| 3 7 |
| 4 8 |


SELECT
COUNT(a.*)
CASE WHEN 7 IN (a.column) THEN 1 ELSE 0 END AS exist
FROM a
WHERE id > 1


Would return

+--------------------+
| COUNT(*) exist |
+--------------------+
| 3 1 |


Because there are 3 entries with an id > 1 and within the entries and there is an entry with column = 7

Answer

If you want to check if a value exists in the column, you can do:

SELECT COUNT(*),
       MAX(? = a.column) as value_exists
FROM a
WHERE ...

In a numeric context, MySQL treats booleans as integers, with 1 for true and 0 for false. Hence, this returns true if the value is in any row in the column. You can use MIN() if you want to check if the same value is in all the rows.

Comments