Re: Basic SQL join question

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 10 Apr 2005 07:38:23 +0200
Message-ID: <4258bbab$0$1302$636a15ce@news.free.fr>

<b_addams_at_yahoo.com> a écrit dans le message de news:1113105945.745348.3210_at_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
|

select c.sensor_address, c.sensor_location, c.degrees_f, c.update_time,

          l.max_degree, l.min_degree
from current_temperature c,

    ( select sensor_address, max(degrees_f) max_degree, min(degrees_f) min_degree

      from temperature_log
      where system_timestamp > sysdate-1
      group by sensor_address ) l

where l.sensor_address = c.sensor_address /

Regards
Michel Cadot Received on Sun Apr 10 2005 - 00:38:23 CDT