I have had my cup of coffee, but cannot come up with a good solution for the following problem - so I am here to pick your brain. ;-)
Here is what I am trying to accomplish (for PostgreSQL):
- Got a table with logged communications related to products.
- The table contains an ID (PK, serial), date/time when inserted, a product ID, an error code field (and some other fields not relevant for now)
- Rows get validated upon reception, and all rows are stored.
- Rows are either accepted or rejected (error code field).
- When accepted, fine, the rows get processed (as a 'step 2' - not relevant for now).
- However, when rejected the sender gets an error message and is requested to correct the data and resend.
- So, when the corrected data is received again and is validated and accepted, we should have a product row that got rejected and a product row that got accepted.
- We have learned that not all senders check these error message and resend their (corrected) data.
So, what I am looking for are these rows for product that were rejected but never sent again correctly - it may happen that rows get send multiple times and rejected multiple times by our validation process.
I have been at it for a couple of hours, but so far without much luck.
What SQL trick could I pull out of the hat to find these rows?