need help writing sql query...

Hi,

Two tables - one to many relationship. Master is 'transaction (TXN)', other is 'status (STATUS)'. For each record in TXN, there may be zero or more records in STATUS. The tables are joined by TXN_ID, which is the PK of TXN and a FK in STATUS. Several different applications work on any given transaction, and they all update the STATUS table. The STATUS table also keeps a log of all status changes, so a logical update to the status of a transaction for a given application is actually an INSERT on the STATUS table, as opposed to an UPDATE. Each record in STATUS says "application 'x' updated status of transaction
'y' to 'this' on 'somedate'".

I've been able to grab the latest status update by using a 'timestamp' column we have in the STATUS table using a subselect like:

AND timestamp = ( SELECT MAX(timestamp)

		    FROM   STATUS
  		    WHERE  bla bla... )

Now, in words, the query I can't quite figure out:

I want to return all the records in TXN that have been marked in the STATUS table as having been validated by the 'Validation' program. That to me says do a regular join -> no problemo.

SELECT *
FROM TXN, STATUS
WHERE TXN.PK = STATUS.FK_PK
        AND STATUS.SYSTEM='VALIDATION' -- a.k.a. a 'process' or
'application'

        AND STATUS.STATUS='COMPLETED'
        AND TXN.TXN_TYPE='RUNTIME_PARAM_1'

The difficult part for me is that I only want to return this row (from the regular join) if the particular transaction does *not* have another record in the STATUS table with certain values - those values that would indicate that we are already done processing this transaction ('COMPLETED'), or that the transaction has been put into
'ERROR' status.

So, I need to add to the query above, something like: (but don't include this transaction if it is COMPLETED or DONE for this process)

      AND STATUS.SYSTEM='RUNTIME_PARAM_2'
      AND STATUS.STATUS NOT IN('COMPLETED','ERROR')

So, we need the regular join, and if there are no other records at all for this transaction, return the transaction. If, however, there are other status records for this transaction, then return the transaction only if those status records don't indicate 'COMPLETED' or 'ERROR' as the last status record for the particular application being queried.

So, there will be two runtime parameters - the application/process which is doing work on the transaction being the more interesting one b/c it resides in the STATUS table. The other is the type of transaction we're want to filter on - an attribute of the TXN table.

Thanks for any hints. Received on Fri Jul 18 2003 - 19:06:39 CDT