Basic SQL join question

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