Search code examples
mysql

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.

Here's the SQL Fiddle: 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.

Solution

  • 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