Hi,

Friday's are when I struggle the most to think clearly :(

I have the following tables:

Clients - list of clients
cli_pk (primary key)
cli_name

Plans - a list of all plans
pln_pk
pln_name

ClientPlans - list of plans (Insurance and pension) that a client belongs to
cpl_pk
cpl_clifk (foreign key to Clients table)
cpl_plnfk (foreign key to Plans table)

Employees - list of employees within clients
emp_pk
emp_clifk (foreign key to clients table)

CoverageHistory - list of coverage history for each employee (records for enrolled, beneficiary changed, etc)
cov_pk
cov_ctyfk (foreign key to CoverageHistoryChangeTypes table)
cov_empfk (foreign key to employees table)
cov_cplfk (foreign key to client plans table)

CoverageHistoryDocuments - list of supporting documents for each change in coverage
chd_pk
chd_covfk (foreign key to CoverageHistory table)
chd_pcdfk (foreign key to PlanChangetypeDocuments)

CoverageHistoryChangeTypes - a list of each possible type of coverage change
cty_pk
cty_name

PlanChangetypeDocuments - a list of documents required for each plan and coverage history change type.
pcd_pk
pcd_plnfk (foreign key to Plans)
pcd_ctyfk (foreign key to CoverageHistoryChangeTypes)
pcd_document

For some sample data:
Clients
1, Client1
2, Client2

Plans
1, plan1
2, plan 2

ClientPlans
cpl_pk, cpl_clifk, cpl_plnfk
1, 1, 1
2, 1, 2
3, 2, 1

Employees
emp_pk, emp_clifk
1, 1
2, 1
3, 2

CoverageHistory
cov_pk, cov_ctyfk, cov_empfk, cov_cplfk
1, 1, 1, 1
2, 2, 1, 1
3, 1, 2, 1
4, 1, 3, 3


CoverageHistoryDocuments
chd_pk, chd_covfk, chd_pcdfk
1, 1, 1
2, 2, 2


CoverageHistoryChangeTypes - a list of each possible type of coverage change
cty_pk, cty_name
1, Enrolment
2, Change of Beneficiary

PlanChangetypeDocuments - a list of documents required for each plan and coverage history change type.
pcd_pk, pcd_plnfk, pcd_ctyfk, pcd_document
1, 1, 1, enrolment document
2, 1, 1, id card
3, 2, 1, enrolment document
4, 1, 2, beneficiary statement

I need to find what documents (if any) are missing for each coveragehistory record.

In the above example (hopefully I've given a good example!) I should get a list like this:

cov_pk, doc_pk, doc_name
1, 2, id card (only the enrolment document was recorded but the id card should have been recorded too)
3, 1, enrolment document
3, 2, id card
4, 3, enrolment document

I hope I've explained this well.

Any help?