simplify query when using select inside case condition in mysql

March 13, 2022

I have this query that works fine.

      SELECT
        a.id,
        CASE
          WHEN h.trimester < (SELECT time FROM some_table WHERE active='1') THEN 'old'
          WHEN h.trimester = (SELECT time FROM some_table WHERE active='1') THEN 'actual'
          WHEN h.trimester > (SELECT time FROM some_table WHERE active='1') THEN 'future'
        ELSE 'Not Found'
        END condition
      FROM schedule h
      JOIN users a
      USING(id)
      WHERE a.mail='$email'
      ORDER BY COLID DESC LIMIT 1

what I want to know is, if there is a simpler way to use the value that I get from the subquery without having to use the whole subquery on every case comparison…

I’m sure there’s someone out there that haves an answer for this 🙂

>Solution :

Have you tried this statement?

SELECT
        a.id,
        CASE
          WHEN h.trimester < b.time THEN 'old'
          WHEN h.trimester = b.time THEN 'actual'
          WHEN h.trimester > b.time THEN 'future'
        ELSE 'Not Found'
        END condition
      FROM schedule h
      JOIN users a 
      USING(id)
      JOIN (SELECT time FROM some_table WHERE active='1') b
      WHERE a.mail='$email'
      ORDER BY COLID DESC LIMIT 1