I have 3 tables in my project:
A Batch is entered into the tblBatches table and then individual jobs associated with the Batch is entered into tblReferenceData. They are both joined accordingly. When the job gets completed all the data associated with the job gets automatically added to the tblJobData table (via an external source)
To ensure the Batch has been completed, I need to check the data in the tblJobData table against the data in tblReferenceData to check that there is a corresponding entry in the tblJobData table for each entry in tblReferenceData for the Batch reference.
If there is a record in tblReferenceData but there isn't a corresponding entry (with the same REF ID) in tblJobData the user needs to be informed of what Job is missing.
Hope I have explained this ok.