Cyril N. Cyril N. - 3 months ago 6
MySQL Question

MySQL Query to get a list of events with at least 3 different status?

I have a database containing two tables :

Events

(id, name, is_active)


Event_logs
(id, event_id, status, message)


I'd like to list all the events that had at least 3 different
status
logs in it and is still active (
is_active=1
) and I can't come up with the correct
MySQL
query to do that.

(I can add more details if needed, just ask :) ).

Here's the SQLFiddle : http://sqlfiddle.com/#!9/a7442

In that example, I'm looking to get the event ID 2 because it contains 3 different status in event_log : INFO, WARNING and ERROR.
Event ID 1 and 3 contains less than 3 and so are excluded.

Thank you for your help!

Answer

Maybe more coffee is required...

SELECT e.*
  FROM events e
  JOIN event_logs l
    ON l.event_id = e.id
 WHERE is_active=1 
 GROUP 
    BY e.id
HAVING COUNT(DISTINCT l.status) >=3
Comments