469,087 Members | 1,270 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,087 developers. It's quick & easy.

Assitance requested on syntax with joins (outer)

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
Jul 20 '05 #1
4 1691

"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
Jul 20 '05 #2
"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
Jul 20 '05 #3
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

Jul 20 '05 #4
"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.
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Dave | last post: by
1 post views Thread by Joshua Goodstein | last post: by
2 posts views Thread by Martin | last post: by
1 post views Thread by csomberg | last post: by
NeoPa
reply views Thread by NeoPa | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.