Return Related Status with Original Record 2

[mwake]

I have a TRANSACT table with a transact_id field and a related_transact field. If a payment is voided, the original transaction will have the same entry in both the transact_id and related_transact fields. The void transaction has a different transact_id than the payment record and a different trans type, but has the payment transact_id in the related_transact field
Example:
Payment record
Transact ID=12345
Related Transact=12345
Trans Type=C

Void record
Transact ID=12346
Related Transact=12345
Trans Type=Y

I need to eliminate all voided transactions from my report. How do I select the related transact from the Void record and add it to the original Payment record so I can filter it out??

 

[Olaf Doschke]

Use a NOT EXISTS subquery:

[pre]SELECT * FROM TRANSACT as T1 WHERE TransType='C' AND NOT EXISTS (SELECT * FROM TRANSACT as T2 WHERE T2.RelatedTransact = T1.TransactID AND T2.TransType='Y')[/pre]

Bye, Olaf.

 

[mwake]

Thanks Olaf. That was just what I needed...