CODEWITHSUNDEEP
mysql
Envious
Envious

Reputation: 487

Returning data from 2 tables if condition matches

I'm not really sure how to do this. I have website that tracks when a server goes down. So I have 1 table with the server names and ID's named servers and another where the error messages are held called errors. I want to return a calendar like view for the past 7 days that would show if an error occurred in any of our servers.

So far, I have a query that will find error messages for that day for any one server, but I don't know how to return the servers that are good and had 0 errors. 

SELECT errors.error_id, servers.server_id, errors.start_time, servers.name
FROM errors
INNER JOIN servers ON errors.server_id=servers.server_id
WHERE errors.start_time BETWEEN '2014-02-25 00:00:00' AND '2014-02-25 23:59:59'

I have it loop through the 7 days and that all works. But I'm stuck on how to get the id's and names of the servers that DID NOT go down on that day. I've been thinking about implementing an IF or CASE into the query, but I've never used them before and I'm not quite sure how that would work.

Do I need to run multiple queries for this or is it possible with one?

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Instead of looping through the days, do them all at once. Assuming you have at least one error per day, you can get this information from the errors table. Otherwise, you might need a calendar table for this:

SELECT dates.thedate, e.error_id, s.server_id, e.start_time, s.name
FROM (select distinct date(start_time) as thedate
      from errors
      where e.start_time BETWEEN '2014-02-25 00:00:00' AND '2014-03-03 23:59:59'
     ) dates cross join
     servers s LEFT OUTER JOIN
     errors e
     ON e.server_id = s.server_id;

This will generate a row for each error for each server per day. If there is no error, there will be a row for each server with NULL in the error fields. If you want to aggregate this:

SELECT dates.thedate, s.server_id, s.name, count(*) as numErrors,
       group_concat(error_id order by e.start_time) as errorIds,
       group_concat(se.tart_time order by e.start_time) as startTimes
FROM (select distinct date(start_time) as thedate
      from errors
      where e.start_time BETWEEN '2014-02-25 00:00:00' AND '2014-03-03 23:59:59'
     ) dates cross join
     servers s LEFT OUTER JOIN
     errors e
     ON e.server_id = s.server_id and date(e.start_time) = dates.thedate
GROUP BY dates.thedate, s.server_id, s.name;

EDIT:

Without a calendar table, you can insert each day into the query like this:

SELECT dates.thedate, s.server_id, s.name, count(*) as numErrors,
       group_concat(error_id order by e.start_time) as errorIds,
       group_concat(se.tart_time order by e.start_time) as startTimes
FROM (select date('2014-02-25') as thedate union all
      select date('2014-02-26') union all
      select date('2014-02-27') union all
      select date('2014-02-28') union all
      select date('2014-03-01') union all
      select date('2014-03-02') union all
      select date('2014-03-03') 
     ) dates cross join
     servers s LEFT OUTER JOIN
     errors e
     ON e.server_id = s.server_id and date(e.start_time) = dates.thedate
GROUP BY dates.thedate, s.server_id, s.name;

Upvotes: 1

Related Questions