Desperate for assistance on a join:

[Joshua Goodstein]

I have been working for a month (on and off) on the following issue:

The table contains event data, each event is placed in the table as
the event occurs, if there is never an event then there is never an
enttry for said event. When a device is down it is status 1 when it
comes back up the device becomes status 6. the newest status will have
a newer timestamp. So the objective is to write a querey that searches
that table for all the devices that are down (at this point in time).
Logically it would be select all the information from the table, where
the status is 1 and there is no newer event for that device. Here's
what I've come up with, however I get an error every single time. I'm
to the virge of snapping and I don't know why.


This is the create statement for the table:

CREATE TABLE `eventlog` (
`date_time` timestamp(14) NOT NULL,
`event_id` int(11) default NULL,
`priority` int(11) default NULL,
`message` varchar(255) default NULL,
`map_id` varchar(32) default NULL,
`agent_addr` varchar(16) default NULL,
`from_addr` varchar(16) default NULL,
`community` varchar(255) default NULL,
`enterprise` varchar(128) default NULL,
`up_time` int(11) default NULL,
`trap_oid` varchar(128) default NULL,
) TYPE=MyISAM;

here is my select:


Select d.* from
( select agent_addr, max(date_time) from eventlog where priority = 1
group by agent_addr) d,
(select agent_addr, max(date_time) from eventlog where priority = 6
group by agent_addr) u
where d.agent_addr = u.agent_addr
and d.date_time >= u.date_time


Here is the error:

[slaweb] ERROR 1064: You have an error in your SQL syntax. Check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'select agent_addr, max(date_time) from eventlog
where priority

[Bill Karwin]

Re: Desperate for assistance on a join:

Joshua Goodstein wrote:
[color=blue]
> Logically it would be select all the information from the table, where
> the status is 1 and there is no newer event for that device.[/color]

Try this query:

SELECT down.agent_addr , down.date_time
FROM eventlog AS down LEFT OUTER JOIN eventlog AS up
ON (down.agent_add r = came_up.agent_a ddr
AND up.date_time > down.date_time)
WHERE down.priority = 1 AND up.event_id IS NULL;
[color=blue]
> I get an error every single time. I'm
> to the virge of snapping and I don't know why.[/color]

Subqueries are implemented in MySQL 4.1. If you're running an earlier
version, the subqueries you were using would cause an error.

Regards,
Bill K.