Assitance requested on syntax with joins (outer)

[Joshua Goodstein]

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.access_time >= u.access_time
The above statement is to get certain data from an event table. I need
all of the lines where the event is 1 and there are no newer event 1's
or event 6's

I need to know what devices are currently down (Priority 1) based on
this history table. where there is no newer event 1 entries for the
agent_addr, or there is no newer priority 6's for that agent_addr
(priority 6 means device is back up)

I'm stumped on the syntax to do joins, or to get this to work.

the table works like the following
device goes down entry is made as priority 1 eventid 1
device comes back up new endtry is made priority 6 eventid 2
device goes back down another entry is made as priority 1. eventid 3

I need to make sure i only am getting a list of the latest status of
each device in the aformentioned scenario it would be the one with
event ID 3

I really appreciate any assistance at all. Thanks in advance

Joshua G

[Peter]

"Joshua Goodstein" <aw*********@yahoo.com> wrote in message
news:92**************************@posting.google.c om...

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.access_time >= u.access_time

Try this
Select a.*
From eventlog a,
( Select agent_addr, max(date_time) as date_time
From event_log
Group by agent_addr) b
Where a.agent_addr = b.agent_addr and a.date_time = b.date_time and
priority = 1

That will give the devices that are "currently" down

Hope it works

HP

[Joshua Goodstein]

"Peter" <he*************@comcast.net> wrote in message news:<wY********************@comcast.com>...

"Joshua Goodstein" <aw*********@yahoo.com> wrote in message
news:92**************************@posting.google.c om...

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.access_time >= u.access_time

Try this
Select a.*
From eventlog a,
( Select agent_addr, max(date_time) as date_time
From event_log
Group by agent_addr) b
Where a.agent_addr = b.agent_addr and a.date_time = b.date_time and
priority = 1

That will give the devices that are "currently" down

Hope it works

HP

[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) as date_time
From event_log
G

I get that error, anyother ideas ?

i aprreciate the assistance i truly do

[Peter]

Joshua

In my post there was a typo

Select a.*
From eventlog a,
( Select agent_addr, max(date_time) as date_time
From event_log // SHOULD BE EVENTLOG
Group by agent_addr) b
Where a.agent_addr = b.agent_addr and a.date_time = b.date_time and
priority = 1

The query shoud be (please check the table names!!!!
Select a.*
From eventlog a,
( Select agent_addr, max(date_time) as date_time
From eventlog
Group by agent_addr) b
Where a.agent_addr = b.agent_addr and a.date_time = b.date_time and
priority = 1
HP


"Joshua Goodstein" <jg********@gmail.com> wrote in message
news:a5**************************@posting.google.c om...

"Peter" <he*************@comcast.net> wrote in message

news:<wY********************@comcast.com>...

"Joshua Goodstein" <aw*********@yahoo.com> wrote in message
news:92**************************@posting.google.c om...

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.access_time >= u.access_time

Try this
Select a.*
From eventlog a,
( Select agent_addr, max(date_time) as date_time
From event_log
Group by agent_addr) b
Where a.agent_addr = b.agent_addr and a.date_time = b.date_time and
priority = 1

That will give the devices that are "currently" down

Hope it works

HP

[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) as date_time
From event_log
G

I get that error, anyother ideas ?

i aprreciate the assistance i truly do

[Joshua Goodstein]

"Peter" <he*************@comcast.net> wrote in message news:<eP********************@comcast.com>...

Joshua

In my post there was a typo

Select a.*
From eventlog a,
( Select agent_addr, max(date_time) as date_time
From event_log // SHOULD BE EVENTLOG
Group by agent_addr) b
Where a.agent_addr = b.agent_addr and a.date_time = b.date_time and
priority = 1

The query shoud be (please check the table names!!!!
Select a.*
From eventlog a,
( Select agent_addr, max(date_time) as date_time
From eventlog
Group by agent_addr) b
Where a.agent_addr = b.agent_addr and a.date_time = b.date_time and
priority = 1
HP


"Joshua Goodstein" <jg********@gmail.com> wrote in message
news:a5**************************@posting.google.c om...

"Peter" <he*************@comcast.net> wrote in message

news:<wY********************@comcast.com>...

"Joshua Goodstein" <aw*********@yahoo.com> wrote in message
news:92**************************@posting.google.c om...
> 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.access_time >= u.access_time

Try this
Select a.*
From eventlog a,
( Select agent_addr, max(date_time) as date_time
From event_log
Group by agent_addr) b
Where a.agent_addr = b.agent_addr and a.date_time = b.date_time and
priority = 1

That will give the devices that are "currently" down

Hope it works

HP

[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) as date_time
From event_log
G

I get that error, anyother ideas ?

i aprreciate the assistance i truly do

Unfortunately it is still not working. The independednt selects seem
to work however the joing of them together still gives me errors.