Chris Casad Chris Casad - 1 month ago 9
MySQL Question

MySQL case statement using sub-select values

I'm wondering if the following query can be rewritten so that the column "end_value" returns "isequal", "isless" or "isgreater" depending on the values from the other two dynamic columns.

SELECT
(SELECT cat_id FROM catA WHERE uid = 111) AS user_value,
(SELECT cat_id FROM catB WHERE oid = 222) AS set_value,
CASE
WHEN user_value = set_value THEN 'isequal'
WHEN user_value < set_value THEN 'isless'
WHEN user_value > set_value THEN 'isgreater'
END AS end_value


MySQL currently complains "Unknown column 'user_value' in 'field list'". Is something like this doable?

Thanks

Answer

How about this?

SELECT  catA.cat_id AS user_value, catB.cat_id AS set_value,
        CASE
            WHEN catA.cat_id = catB.cat_id THEN 'isequal'
            WHEN catA.cat_id < catB.cat_id THEN 'isless'
            WHEN catA.cat_id > catB.cat_id THEN 'isgreater'
          END AS end_value
FROM catA, catB
WHERE catA.uid = 111
  AND catB.oid = 222