Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Basic SQL join question

Basic SQL join question

From: <b_addams_at_yahoo.com>
Date: 9 Apr 2005 21:05:45 -0700
Message-ID: <1113105945.745348.3210@l41g2000cwc.googlegroups.com>


I apologize in advance-- this is probably a simple problem but I am not having any luck with this query. Any help would be appreciated.

I have two tables, current_temperature and temperature_log and I am trying to query both in a single SQL statement. The current_temperature table contains a row for each temperature sensor and new rows are never added. The table contains only ten rows. This table is used to store current information about each sensor and is updated every minute. Here is the layout:

 Name                                      Null?    Type
 ----------------------------------------- --------

----------------------------
SENSOR_ADDRESS NOT NULL VARCHAR2(4) SENSOR_LOCATION VARCHAR2(20) DEGREES_F NOT NULL NUMBER(38) DEGREES_C VARCHAR2(8) UPDATE_TIME NOT NULL DATE ERROR_COUNT NUMBER(38) ERROR_TIME DATE

The PK is sensor_address.

The temperature_log table is a historical table that stores the temperature for each sensor every fifteen minutes. The PK is sensor_address and system_timestamp. Here is the layout:

 Name                                      Null?    Type
 ----------------------------------------- --------

----------------------------
SYSTEM_TIMESTAMP NOT NULL DATE SENSOR_ADDRESS NOT NULL VARCHAR2(4) DEGREES_F NOT NULL NUMBER(38) DEGREES_C NOT NULL VARCHAR2(8)

I am trying to create a query that will list info from the current_temperature table and then lookup the max and min temperatures for that sensor within the last 24 hour period. It's pretty easy to get the max and min from the temperature_log table but when I try to combine the querys I get errors.

This query works to pull the info from the temperature log:

select sensor_address, max(degrees_f) from temperature_log where trunc(sysdate) = trunc(system_timestamp) group by sensor_address;

(Yes, I know this is not "last 24 hours"; that part I know how to do. I just had the trunc for simplicity until I get the join correct.)

And of course, the current_temperature query is straightforward:

select sensor_address, sensor_location, degrees_f, update_time from current_temperature;

Any help would be appreciated.

Thanks.
Bill Received on Sat Apr 09 2005 - 23:05:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US