I have the following horrible sql. I need one result that has all the data in one row. I am currently using 3 sub queries and figure it must be a better way...
SELECT 'FS_DEV', ifnull(a.severity, 0) AS aseverity, ifnull(a.eventid, 0) AS aeventid, ifnull(a.description, 'None') AS adescription, ifnull(a.hostid, 0) AS ahostid, ifnull(a.color,'#E5E0DC') as acolor, ifnull(a.fontcolor,0) as afontcolor, ifnull(a.severitydesc,'None') as aseveritydesc, ifnull(p.severity, 0) AS pseverity, ifnull(p.eventid, 0) AS peventid, ifnull(p.description, 'None') AS pdescription, ifnull(p.hostid, 0) AS phostid, ifnull(p.color,'#E5E0DC') as pcolor, ifnull(p.fontcolor,0) as pfontcolor, ifnull(p.severitydesc,'None') as pseveritydesc, ifnull(s.severity, 0) AS sseverity, ifnull(s.eventid, 0) AS seventid, ifnull(s.description, 'None') AS sdescription, ifnull(s.hostid, 0) AS shostid, ifnull(s.color,'#E5E0DC') as scolor, ifnull(s.fontcolor,0) as sfontcolor, ifnull(s.severitydesc,'None') as sseveritydesc FROM (SELECT e.severity, e.eventid, e.description, e.hostid, fs.color, fs.fontcolor, fs.severitydesc FROM fs_events e, fs_severity fs WHERE hostid in (21,22,23,24,15,16) and e.severity = fs.severityid AND e.event_perf = 1 AND e.time_stamp >= date_sub(now(), INTERVAL 30 DAY) AND e.acknowledged in ( 0,1) ORDER BY e.severity DESC, e.time_stamp DESC LIMIT 0, 1) p, (SELECT e.severity, e.eventid, e.description, e.hostid, fs.color, fs.fontcolor, fs.severitydesc FROM fs_events e, fs_severity fs WHERE hostid in (21,22,23,24,15,16) and e.severity = fs.severityid AND e.event_avail = 1 AND e.time_stamp >= date_sub(now(), INTERVAL 30 DAY) AND e.acknowledged in ( 0,1) ORDER BY e.severity DESC, e.time_stamp DESC LIMIT 0, 1) a, (SELECT e.severity, e.eventid, e.description, e.hostid, fs.color, fs.fontcolor, fs.severitydesc FROM fs_events e, fs_severity fs WHERE hostid in (21,22,23,24,15,16) and e.severity = fs.severityid AND e.event_sec = 1 AND e.time_stamp >= date_sub(now(), INTERVAL 30 DAY) AND e.acknowledged in ( 0,1) ORDER BY e.severity DESC, e.time_stamp DESC LIMIT 0, 1) s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]