Help with SQL

From: John Kramer <john_at_ekramer.net>
Date: 7 Jan 2002 15:57:11 -0800
Message-ID: <fbcef4df.0201071557.302c2ad0_at_posting.google.com>

I have two tables that are joined by five fields, though I've simplified the joins down to two for the example below. The first table holds equipment data (EQUIP), and the second (FAIL) holds data that reflects failures that occured in the production on the equipment stored in EQUIP. The EQUIP table stores the times that the equipment was run, and the FAIL table show aggregated data for time periods when there were failures on the equipment. Here's the problem I'm having:

EQUIP Table                            FAIL Table

EquipID MoldNo StartRun EndRun EquipID MoldNo Start End

   Fails

1         2       13:00      15:00     1         2       13:00   
13:05   2 2         2       14:00      15:00     1         2      
13:05    13:10   3
                                       1         2       13:10   
13:15   2
                                       1         2       13:15   
13:20   3
                                       1         2       13:20   
13:25   2
                                       1         2       13:25   

13:30 3
***The Start and End columns in the EQUIP table represent the time
period that the equipment was run.
***The Start and End columns in the FAIL table represent 5 minute
increments for recording failures
*A record can only exist in the FAIL table if there is a record in the EQUIP table for the same time period, but no record necessarily exists in the FAIL table if one exists in the EQUIP table. In other words, a record is written into the FAIL table only when a failure occurs. If no failure occurs, no record is written.

I'd like to get a result set that displays all equipment used during a time period (lets say between 13:00 and 15:00 for this day) and the sum quantity of fails that occurred on that equipment during the sepcific time period that it was run. If there were no fails on that equipment for that time period that it was run, I'd like it to show that there were no errors. Here's the resultset I'd hope to return for the previous data sets:

EquipID     MoldNo    Start     End       Fails
1           2         13:00     15:00     15
2           2         14:00     15:00      0

Everything I've tried will only records from the EQUIP table that have matching records in the FAIL table. I just can't see how to get this to work the way I'd like to. Do I need to use PL/SQL to accomplish this?

Your help and expertise is greatly appreciated. Received on Tue Jan 08 2002 - 00:57:11 CET