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]
