MySQL 5.7. Given a table of vehicles, and a table of alerts, with a 1:N relationship. A vehicle could have no alerts, or multiple alerts in a mixture of closed and open states. If alert.closed_time is NULL, the alert is considered 'OPEN'.

To collapse all alerts to a single status value for each vehicle prioritising 'OPEN' alerts, I have been doing the following:

SELECT
  CASE
    WHEN SUM(CASE WHEN a.closed_time IS NULL THEN 1 ELSE 0) END) > 0 THEN 'OPEN'
    ELSE 'CLOSED'
  END AS a.status,
  v.id
FROM alerts a JOIN vehicles v ON a.vehicle_id = v.id
GROUP BY v.id;

Which seems to be working OK. However, it strikes me there ought to be a simpler/more efficient way than nested CASE statements! It's important that if there is any NULL value in the column, I prioritise it over any timestamps. I did wonder about something like:

GROUP_CONCAT(COALESCE(a.closed_time, 'ACTIVE'))

and then looking for strings containing 'ACTIVE' in the column, but I'm not sure that's any better.

CodePudding user response：

Given that you ultimately need to translate a datetime value into a string, I don't think it's possible with a single function call. However, you could simplify things a bit by using a conditional MAX() to return 1 if there are any open alerts, otherwise 0. Then use IF() to translate the boolean result into the statuses: "OPEN" or "CLOSED"

SELECT
  IF(MAX(a.closedTime IS NULL) = 1, 'OPEN', 'CLOSED') AS status
  , v.id
FROM alerts a JOIN vehicles v ON a.vehicle_id = v.id
GROUP BY v.id
ORDER BY v.id

Results:

db<>fiddle here