473,883 Members | 1,796 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1892

"Joshua Goodstein" <aw*********@ya hoo.com> wrote in message
news:92******** *************** ***@posting.goo gle.com...
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******* *************@c omcast.com>...
"Joshua Goodstein" <aw*********@ya hoo.com> wrote in message
news:92******** *************** ***@posting.goo gle.com...
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********@gma il.com> wrote in message
news:a5******** *************** ***@posting.goo gle.com...
"Peter" <he************ *@comcast.net> wrote in message

news:<wY******* *************@c omcast.com>...
"Joshua Goodstein" <aw*********@ya hoo.com> wrote in message
news:92******** *************** ***@posting.goo gle.com...
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******* *************@c omcast.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********@gma il.com> wrote in message
news:a5******** *************** ***@posting.goo gle.com...
"Peter" <he************ *@comcast.net> wrote in message

news:<wY******* *************@c omcast.com>...
"Joshua Goodstein" <aw*********@ya hoo.com> wrote in message
news:92******** *************** ***@posting.goo gle.com...
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
248506
by: Dave | last post by:
I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle. I need to update a field in the NAMEINFO table for a particular surname in a particular town. I can select the records fine with this syntax (testing in Oracle SQL* Plus) SELECT NAMEINFO.LASTNAME, NAMEINFO.FIRSTNAME, NAMEINFO.MIDDLENAME, NAMEINFO.GENDER, ADDRESSINFO.REGION FROM NAMEINFO, ADDRESSINFO...
1
539
by: Joshua Goodstein | last post by:
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
2
18117
by: Martin | last post by:
I am now working on SQL Server 2000 having had previous experience on a different database. Both of the OUTER JOIN syntaxes is different from what I am used to and I am finding it slightly confusing. For example, given two tables : wipm_tbl_mi wipm_tbl_wi (which may not have data in it for a specific record that exists in the first table.)
3
44544
by: mheydman | last post by:
I apologize if this has been asked before- I searched google but could not find a concrete answer. I recently inherited a database whose t-sql code is written in a format that I find difficult to read (versus the format I have used for years). I have tested the queries below using the SQL Profiler, and both have identical costs. Is there any advantage of one format over the other?
1
1142
by: csomberg | last post by:
SQL Server 2000 When joining tables, some use: AliasTableName.ColumnName = AliasTableName.Column while others use LEFT/RIGHT etc JOIN ....... Is one better than the other in cases where the join is a simple JOIN of equality ?
3
17846
by: Doug | last post by:
Hi, I'm more familiar with MSSQL than Access syntax and have run into a problem with correctly putting ( )'s around the joins in a 3 table query. I want to INNER JOIN lenders and accounts and LEFT OUTER JOIN that result with prospects. (I want to receive all the results of the inner join and any pertinent info from table 3 that is available.) The way it was written in MSSQL was basically..
4
1583
by: Brian Parker | last post by:
I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN working on a three table query. Assumptions: -- I have events in the Event table. -- Each event CAN have one Transaction, but it's not guaranteed -- Each transaction, ir present, will have one or more Amount records This would be the pseudo-query without any special joins:
1
3121
by: imranpariyani | last post by:
Hi i have a severe performance problem with one of my views which has 6 to 8 joins .. any help will be appreciated.. the view is: CREATE OR REPLACE VIEW thsn.trade_view AS SELECT tra.tra_id, tra.per_id, tra.fir_id, tra.tra_dcn, tra.tra_startdate::date AS tra_startdate, tra.tra_enddate::date AS tra_enddate, tra.tra_highprice, tra.tra_lowprice, tra.tra_shares, tra.tra_marketvalue, tra.tra_commonsharesheld, tra.tra_directsharesheld,...
4
52469
MMcCarthy
by: MMcCarthy | last post by:
To view Access queries in SQL rather than Access query design - open the query design window and change the view to SQL: Select Statement SELECT FROM ; Append Statement INSERT INTO (, , ) VALUES ('value1', #value2#, value3); This assumes value1 is a string, value2 is a date and value 3 is some other datatype
0
18525
NeoPa
by: NeoPa | last post by:
Introduction Joins, in SQL, are a way of linking Recordsets together. They involve restricting which data is returned in the output Recordset. When no join is specified but two Recordsets are, then a cartesian product is produced which specifies no restrictions. Conceptually, a JOIN is applied before any WHERE clause which may be specified. NB. Full Outer Joins are not supported in Access (Jet) SQL. When Recordsets are JOINed they...
0
9942
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11142
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10743
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10847
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10416
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9574
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7129
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
4220
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3233
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.